3

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

2

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

  • Wouldn't it be more efficient to do the st_transform on the one point rather than all the multipolygons? – mlinth Jun 14 '16 at 9:07
  • I suppose it depends on the size of the polygon, but I would also say to store the polygon data in 4326 and only transform to 2263 when needed for area/length/etc.... – DPSSpatial Jun 14 '16 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.