postgispostgresql
This line:
ST_AsText(ST_Transform(the_geom, 4326))
Returns the "coordinates" as utm coordinates. Although, I thought ST_Transform was supposed to convert the coordinates from utm to lat/lon. I found a tutorial where the behavior I need is shown in an example: http://postgis.net/workshops/postgis-intro/projection.html
Is there any reason why it would not be doing this?
The input data is in EPSG 26918 (UTM zone 18N). So if you want to query against that, you need to use that spatial reference system. If you want to ask in longitude / latitude, you need to transform that into 26918, which is easy to do with ST_Transform.
The conversion looks like:
SELECT ST_Transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',4326), 26918)
So if you want to find any area that contains that particular point:
SELECT * FROM es_zones WHERE ST_Contains(geom, ST_Transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',4326), 26918))
The PostGIS manual explains well the difference between geometry and geography and why ST_Distance gives different results in chapter 4 http://postgis.net/docs/using_postgis_dbmanagement.html#PostGIS_Geography.
ST_Distance with geography type gives as a result the great circle length that follows the surface of the globe (actually WGS84 spheroid). ST_Distance with geometry makes distance calculations as if the globe were flat as in the image below. You can measure with a ruler that both Greenland and Africa are about 60 degrees wide but only by using geography based distance you can see that 60 degrees at the equator is 6668 km while at 80 degrees N it is only 1106 km.
Best Answer
Thanks for the help everyone. I guess the all the geometries in my database were set to the wrong SRIDs. The geometries had SRIDs of 4326 when they should have been 3400, and this is why ST_Transform wasn't doing anything.
This is the code that ended up giving me what I wanted: