Finding row that has shapefile containing lat/long point in PostGIS geometry column

Question

I am fairly new to PostGIS and I'm trying to return a row in my table that has the shapefile containing a lat/long point I pass in via st_contains. Below is a query I wrote myself that brings back 0 rows with no errors as seen below.

SELECT * 
FROM es_zones 
WHERE ST_Contains(geom, ST_GeomFromText('POINT(-73.952545 40.774576)'))=true;

Having found a query similar of that to which I am trying to accomplish here, below is my last query ran.

SELECT * 
FROM es_zones 
WHERE ST_Contains(es_zones.geom,ST_transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',26918),26918))=true 
AND ST_Distance(es_zones.geom,ST_transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',21918),21918))=false;

which also brings back 0 rows as seen below

----+-------------+-------+--------+--------------+-------------+--------------+-------------+---------------+---------------+-----------+-----+----------+-------+--------

gid | schooldist | boro | initials | creat_date | edit_date | zoned_dist | boro_num | shape_leng | shape_area | remarks | dbn | esid_no | label | geom 

(0 rows)

Really not sure where I am going wrong.

I was able to get QGIS set up and connected to my PostGIS data. I don't understand what the 997487, 221261 coordinates are.

How do they correspond to the lat/lon points I got back from my GeoCoder Gem?

I passed them in as my point but got the following error;

ERROR: Operation on mixed SRID geometries

Verifying the SRID shows

SELECT ST_SRID(geom) FROM es_zones LIMIT 1;
 st_srid 
---------
  926918
 (1 row)

So it sounds like something may have happened to my spacial_ref_sys table, or perhaps I didn't get it configured correctly to begin with. A Google search for my last error ERROR: GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys returned this StackOverflow question. Which points to running spatial_ref_sys.sql to regenerate the rows in that table.

I found some elaboration on how to go about executing spatial_ref_sys.sql in the PostGIS documentation here, but I'm confused why it says it can't find SRID 4326 when looking in PGAdmin I can see it as shown below!

Answer 1

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))

Answer 2

I would suggest taking a look at your dataset visually (you could connect to your database using QGIS for example). That way you can check if a point would actually be in the polygon, and as @BradHards suggests, check that you have the correct lon/lat when constructing the geometry.

Your query looks fine - it's not necessary to have true at the end. You can just do something like this:

SELECT * 
FROM es_zones 
WHERE ST_Contains(geom, ST_GeomFromText('POINT(lon lat)'));

Answer 3

SELECT 
  ST_X(point) AS longitude, ST_Y(point) AS latitude 
FROM 
( 
  SELECT 
    ST_AsText(ST_Centroid(the_geom)) AS point 
  FROM 
    "28DSE250GC_SIR" 
  LIMIT 
    1
) AS subquery