0

I have a cities shapefile and a counties shapefile. I have the counties shapefile imported to a PostgreSQL table. I haven't transferred my cities to PostgreSQL yet, but the issue I really want to figure out is how do I create a third table.

Since cities and townships often overlap several counties, I ultimately want a cities table, a counties table, and a city_county pivot table (ID, CITY_ID, COUNTY_ID) so it will play fair with my web application (Laravel Eloquent ORM).

I've been looking through QGIS and can't seem to figure it out, but even if I can accomplish this with SQL in PostgreSQL/PostGIS, that'd be great.

  • 1
    This is just a simple spatial join in CREATE TABLE AS query. Load the second table and start by researching a 1:M/M:M join on ST_Intersects. – Vince Oct 25 '15 at 0:45
2

What you want to do is something like this - replace 4326 with the srid of your data.

CREATE TABLE city_county AS
 SELECT ROW_NUMBER() OVER(ORDER BY cities.gid, counties.gid) As gid,
         cities.gid As city_id, counties.gid AS county_id,
         ST_Multi(
              ST_Intersection(counties.geom, cities.geom)
              )::geometry(Multipolygon, 4326)  AS geom
FROM cities INNER JOIN counties 
ON (ST_Covers(counties.geom, cities.geom)  
      OR ST_Overlaps(counties.geom, cities.geom ) );

Note I use overlap and covers instead of ST_Intersects because you don't want to join couties and cities that just share a border.

Then you just add a spatial index to your geometry column and and primary key and you should be good to go.

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.