The zillow data is in lat/lon, so use -s 4326. lat/lon is not a planar projection, so the units returned are pretty non-sensical: square degrees for area, linear degrees for distance.
Once you have your data correctly flagged with respect to its source projection, you can transform it. Add a new entry for a continental USA projection: INSERT INTO SPATIAL_REF_SYS VALUES (1000000,'ME',1000000,'','+proj=aea +lat_0=23 +lon_0=-96 +lat_1=29.5 +lat_2=45.5 +datum=NAD83 +ellps=GRS80'); Then use it to calculate area: SELECT area(transform(the_geom),1000000) FROM neighborhoods WHERE name = 'Ballard'; P On Wed, May 14, 2008 at 3:13 PM, David Jantzen <[EMAIL PROTECTED]> wrote: > Hi All, > > I'm brand spanking new to the GIS world, so I've got some pretty dumb > questions. If the answer is "RTFM" that's fine, please just be specific > about which documentation to look at. > > What I'm trying to do: > 1) Properly load the Zillow neighborhood shapefiles into a PostGIS > enabled database. > 2) Write some test queries to learn about the dataset and PostGIS > functionality. > 3) Take a lat/long and convert it to the appropriate neighborhood. > > I've made some progress on #1, but I'm not sure I've done it properly > with respect to the SRID. I loaded the Washington state data with this > command: > > shp2pgsql ZillowNeighborhoods-WA.shp neighborhoods | psql GeoTest -U > postgres > > The data loads without error and geometry_columns now contains: > > f_table_name | f_geometry_column |coord_dimension | srid | type > -----------------+----------------+---------------+-------------------+-- > neighborhoods | the_geom | 2 | -1 | MULTIPOLYGON > > I'm concerned that I have to specify the correct SRID, however I don't > see it anywhere in the Zillow .prj files, so I'm letting it default to > -1 hoping it will be magically correct. I suspect this may be > problematic. > > Next, I'm attempting some basic queries, such as: > select area(the_geom) from neighborhoods where name = 'Ballard'; > area > ---------------------- > 0.000461824752619577 > > select distance((select the_geom from neighborhoods where name = > 'Ballard'),(select the_geom from neighborhoods where name = > 'Georgetown')); > distance > -------------------- > 0.0982236357558739 > > My question is, what do these return values *mean*? I read in the FAQ > that "ST_Distance always returns in the units of the projection." which > I assume applies to other functions as well. And my understanding is > that the units of the projection is determined by the SRID, and if > that's wrong then any conclusions derived from it would be wrong as > well. Right? So I'm kind of stuck here. Is the -1 SRID really a > problem? How do I find the real SRID if it is? > > Thanks for your help, > David > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
