ST_Within and ST_Contains not working for Latitude and Longitude in PostGIS

Question

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.

Answer 1

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)