[GIS] wrong with how I am using ST_Within

postgis

I am learning postgis 2.1. I created a postgis table from a shapefile and imported it into the DB. It made a table like this: 

db=# \d districts 
     ...
     geog       | geography(MultiPolygon,4326) |

I also created a table "callsforservice" importing tabular data from a CSV, which I then converted into points with a SRID = 4326.

db=# update callsforservice set latlongpointdetails = ST_SetSRID(latlongpoint, 4326);

I want to know which callsforservice.latlongpointdetails are included in which districts.geogs

I tried with this query, which takes very, very, very long time. The limit 1 is only to shorten the time the query is running. After 2 min or so I kill it:

select * from callsforservice, districts where ST_Within(geometry(districts.geog), callsforservice.latlongpointdetails) limit 1;

If I add an additional condition to the where clause (where districts.district='1-1') — this query returns immediately with 0 rows, even though districts does have a row with this ID.

Do I have the syntax right for finding out which calls came in what districts?

The docs for ST_within say "the source geometries must both be of the same coordinate projection, having the same SRID." If I examine the two tables with the \dt command then the geog column in districts shows type geography(MultiPolygon,4326) but the latlongpointdetails column in callsforservices only shows type geometry — without parenthesis like in the districts table. Maybe this is the reason for the failures of ST_Within? Where am I going wrong?

Best Answer

You shouldn't be mixing geometry with geography in your query. If all you are going to do is convert your geography to geometry (which is what geometry(districts.geog) does), there isn't any point in having it as geography. What would make more sense is to change your callsforservice.latlongpointdetails to geography type since geography is more correct.

ALTER TABLE callsforservice
  ALTER COLUMN latlongpointdetails TYPE geography 
     USING latlongpointdetails::geography(POINT,4326);

CREATE INDEX idx_callsforservice_latlongpointdetails ON callsforservice USING GIST(latlongpointdetails);

You also want to make sure that your latlongpointdetails is in long,lat (not lat,long). You don't need && if you are comparing apples with apples. ST_Within has a && call built into it. Also I don't think ST_Within is supported for geography (well not listed, though surprise it doesn't), and your ST_Within (should be point within polygon not polygon within point) call looks backward to me so not sure how you are getting anything back. Use ST_Covers instead -- http://postgis.net/docs/manual-2.1/ST_Covers.html

select * from callsforservice 
  INNER JOIN districts 
      ON ST_Covers(districts.geog, callsforservice.latlongpointdetails) limit 1;
Related Question