Find row with a polygon containing a specific lat/long

Question

I have a Postgres table that has two columns – address, and geom (which is a multipolygon without any SRID set, but it should be 2263). I'm trying to find all of the rows that contain a given point, which I only have in lat/long form.

The query I want should look, I think, something like this, but I can't tell if I'm writing it wrong, or I simply don't have any polygons that contain the given point. The point is 40.741431, -74.003334 – the center of Google's building in Manhattan.

SELECT t1.address
FROM t1
WHERE ST_Contains(ST_SetSRID(geometry, 2263), ST_GeometryFromText('POINT(-74.003334 40.741431)', 2263));

What am I doing wrong? I suspect it's an SRID thing... (Or am I doing nothing wrong and my data is just bad?)

Answer 1

I would say the geometry you're creating should be in SRID 4326 (the point from Google),

And you should transform the geometry in 2263 to 4326 to match (or really the other way around), so your query might look like this:

SELECT t1.address  
FROM t1  
WHERE ST_Contains(  
ST_Transform(ST_SetSRID(geometry, 2263), 4326)  
, ST_GeometryFromText('POINT(-74.003334 40.741431)', 4326)  
);

That should be close to what you need...