1

I have a table "tbl_location" in PostGIS which has latitude and longitude, so I created a new column called "point_location" :

update "tbl_location"  set point_location = 
ST_Transform(ST_SetSRID(ST_MakePoint(lng, lat), 4326), 26913);

I have another table which is available here has MultiPolygon field and I am trying to find in which polygon a location(lat/lng) belongs. For that, I wrote the following query:

SELECT a.id, b.csa2010
FROM baltimore."vs14_housing" b, baltimore."tbl_location" a 
WHERE 
ST_Intersects(a.point_location,  b.geom) 
    or 
ST_Contains(a.point_location, b.geom)
    or
ST_Within(a.point_location, b.geom)

It is not returning any records. Data type for geom column in vs14_housing is geometry (MultiPolygon). Let me know what I am doing wrong.

  • 1
    take out the all the quotes in both queries – ziggy Sep 29 '16 at 20:20
  • don't cross-post – Mike T Sep 30 '16 at 6:45
3

Are you using the correct spatial reference ID? The vs14_housing data you referenced is 102685.

If you use your query using the UTM(26913), no results.

But if you change it to 102685, it works. Unless you projected your vs14_housing data (which you failed to mention in the question).

with cte as 
(
    select 1 as id, ST_Transform(ST_SetSRID(ST_MakePoint(-76.6129, 39.3106), 4326), 102685) point_location
    ,st_astext(ST_Transform(ST_SetSRID(ST_MakePoint(-76.6129, 39.3106), 4326), 102685)) wkt 
)
SELECT a.id, b.csa2010
FROM public."vs14_housing" b, cte a 
WHERE 
ST_Intersects(a.point_location,  b.geom) 
    or 
ST_Contains(a.point_location, b.geom)
    or
ST_Within(a.point_location, b.geom)

enter image description here

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.