Finding Points in Polygons

Question

I have a database with one table (ca_licenses) of business addresses and a second table in the public schema (ca_la_la_council) of city council district polygons.

I'm running this query to place the district number from the council table inside the business address table:

UPDATE poursafe.ca_licenses dst
SET prem_council = div_city
FROM public.ca_la_la_council src
WHERE st_within(dst.geom,src.geom) AND dst.geom&&src.geom;

My problem is I keep getting zero results. Both geom columns are geometry type and the SRID is 4326.

#create poursafe.ca_licenses table

CREATE TABLE ca_licenses (
id INTEGER,
license VARCHAR,
lat DOUBLE PRECISION,
lon DOUBLE PRECISION,
geom GEOMETRY,
prem_council VARCHAR
);

#create public.ca_la_la_council table

CREATE TABLE ca_la_la_council (
gid INTEGER,
div_city VARCHAR,
shape_leng NUMERIC,
shape_area NUMERIC,
geom GEOMETRY
);

Sample ca_licenses data: https://raw.githubusercontent.com/PourSafe/data_samples/master/ca_licenses.csv

Sample ca_la_la_council data: https://raw.githubusercontent.com/PourSafe/data_samples/master/ca_la_la_council.csv

I need to populate the ca_licenses table column 'prem_council' with the council district number 'div_city'. To do this I'm running the above query which finds which council district the ca_licenses points lie within.

I created a spatial_ref_sys table for the public.ca_la_la_district table but, do not have a spatial_ref_sys table for the ca_licenses table. Do I need one for this table as well? I believe the SRID for both geom columns is set at 4326.

I'm using postgis 2.4.4 and postgres 9.2 on a centos 7 setup.

Could you also provide 1) create table statements for these tables, 2) some sample data and 3) the expected result. This will significantly your chances to get an answer ;-) cheers! — Jim Jones
I updated the original question with all of the above. Thanks! — civarchive
I just created your environment: the geometries are very far away from each other and do not overlap at all, and as I can see the first condition in your where clause is ST_Within, which in this case will always return false. Using QGIS you could see it with more details. Write a comment if you need help with it. — Jim Jones
You were very helpful Jim Jones. I had (assumed) the council SRID was 4326 when in fact it was 2229. I altered the projection with the below and all works great. Thanks!: ALTER TABLE public.ca_la_la_council ALTER COLUMN geom TYPE geometry(Polygon,4326) USING ST_Transform(ST_SetSRID(geom,2229),4326); — civarchive
I guess it should become the first rule of GIS: in case the geometries do not match, check SRS or coordinate pair order (x,y <> y,x). Glad it helped :-) Cheers! — Jim Jones

Naor Tedgi Naor Tedgi · Accepted Answer · 2018-09-16T15:56:57

First

i want to recommend you to check QGIS

works great with wine on linux machines

second

there isn't any problem with your code after loading polygons file into qgis this is the shapefile structure

and then i loaded the points file

the problem is that there isn't any intersection between them the cause is probably a wrong construction of the points.

(the little pink dot its the ca_licenses.csv points)

Last thing from my experiance the best way to intersect polygons with points is using ST_INTERSECT

select * from ca_licenses join ca_la_la_council on st_intersects(ca_licenses.geom,ca_la_la_council.geom)

Finding Points in Polygons

1 Answers