Point-In-Polygon in PostGIS

I was trying to get a PiP (point-in-polygon) done with all the GIS data that I had all of which was either Polygon or Multipolygon format.

I stored counties boundaries (Current County and Equivalent layer) I got from the Census Bureau in tables by state (for the sake of better organization). The data is in Geographic NAD 1983 (basically unprojected data).

You can see which county your lat/long falls into by using the following query:

select *
from la_counties
where st_contains(la_counties.the_geom,transform(GeometryFromText(‘POINT(-90.000 30.500)’,4326), 4269))=true and st_distance(la_counties.the_geom,transform(GeometryFromText(‘POINT(-90.000 30.500)’,4326), 4269))=0.00;

Executing the above query will return you one row with the results containing the county that the lat/long falls into. Great! Now we’ve got the PiP method down pat.

Okay. Now what if you have more layers that just counties, say ou have cities,  commercial districts, fire districts, and so on and so forth. You would have to query those spatial tables separately….right?
Well, yeah, if you choose to go the route of storing the data for each layer separately. But consider this route…..store all your layers in one spatial table (for each state/province). That way when you query that table with the same query above, you will get multiple rows…one from each layer!
Now you do not have to store all your layers in separate spatial tables each!

There is however a tiny issue when your GIS data is coming in from multiple sources….they dont quite follow a standard and align right next to one another…do they?
Now that is a conundrum for me to address another day!

I was trying to get a PiP (point-in-polygon) done with all the GIS data that I had all of which was either Polygon or Multi-polygon format. I stored counties boundaries (Current County and Equivalent layer) I got from the Census Bureau in tables by state (for the sake of better organization). The data is in Geographic NAD 1983 (basically unprojected data).

You can see which county your lat/long falls into by using the following query:

select *
from la_counties
where st_contains(la_counties.the_geom,transform(GeometryFromText(‘POINT(-90.000 30.500)’,4326), 4269))=true and st_distance(la_counties.the_geom,transform(GeometryFromText(‘POINT(-90.000 30.500)’,4326), 4269))=0.00;

Executing the above query will return you one row with the results containing the county that the lat/long falls into. Great! Now we’ve got the PiP method down pat.

Alright, now what if you have more layers that just counties, say ou have cities,  commercial districts, fire districts, and so on and so forth. You would have to query those spatial tables separately….right?Well, yeah, if you choose to go the route of storing the data for each layer separately. But consider this route…..store all your layers in one spatial table (for each state/province). That way when you query that table with the same query above, you will get multiple rows…one from each layer!

There you go! Now you do not have to store all your layers in separate spatial tables each.
There is however a tiny issue when your GIS data is coming in from multiple sources….they dont quite follow a standard and align right next to one another…do they? Now that is a conundrum for me to address another day!

Advertisements

Comments are closed.

%d bloggers like this: