1

I have two shapefiles - Parcels2016 and Base_Zoning_B

Parcels2016 has about 660,000 records with over one hundred attributes (about ~2GB) , so performing spatial joins in QGIS is very inefficient, which is why I am trying to use PostGIS. I would like to find out what is wrong with the SQL command that I used to join attributes by location. If we look at the images below, the Base_Zoning_B layer is in green:

enter image description here

My goal is to join the attributes of Base_Zoning_B to the individual land parcels in Parcel2016 (in blue) and keep both matching and non-matching records:

enter image description here

Both commands I have used in PostGIS failed to produce the correct results as not all of the polygons were joined and it seems only matching records were kept, and not all of them either:

enter image description here

As you can see, in the testlayer, which I exported from PostGIS as a shapefile, there are many parcels excluded. I have executed both of these commands in PostGIS, one with a left join, one without, but both produce the same incomplete results:

CREATE TABLE test AS
SELECT t.gid, t.geom, m.zone_dist, m.pd_num, m.council_da, m.long_zone_, m.globalid, m.ord_num,
m.notes, m.res_num, m.effectived, m.case_numbe, m.common_nam, m.districtus, m.cd_num
from parcel2016 AS t, base_zoning_b AS m
where st_within(t.geom, m.geom) AND ST_IsValid(t.geom) AND ST_IsValid(m.geom)

CREATE TABLE test AS
SELECT t.gid, t.geom, m.zone_dist, m.pd_num, m.council_da, m.long_zone_, m.globalid, m.ord_num,
m.notes, m.res_num, m.effectived, m.case_numbe, m.common_nam, m.districtus, m.cd_num
FROM parcel2016 AS t
LEFT JOIN base_zoning_b AS m
ON ST_Within(t.geom, m.geom)
WHERE ST_IsValid(t.geom) AND ST_IsValid(m.geom)

I also received the following warning messages:

NOTICE:  Ring Self-intersection at or near point 2489538.1553773778 7045488.56387801
NOTICE:  Ring Self-intersection at or near point 2458823.8434288907 7006314.9953470686
NOTICE:  Ring Self-intersection at or near point 2497326.4063920071 7047098.5640874337
NOTICE:  Ring Self-intersection at or near point 2509123.1707222643 6916240.9188464647
NOTICE:  Ring Self-intersection at or near point 2485148.4026713888 6957535.547359148
NOTICE:  Ring Self-intersection at or near point 2468946.5905914963 6947132.4664672073
NOTICE:  Ring Self-intersection at or near point 2483211.8534999937 6963347.7294999957 0
NOTICE:  Ring Self-intersection at or near point 2500677.5199999958 6921843.0100000054 0
NOTICE:  Ring Self-intersection at or near point 2505740.7239122842 7002511.0560633009
NOTICE:  Ring Self-intersection at or near point 2476637.4922218379 6988535.7446923936
NOTICE:  Ring Self-intersection at or near point 2539170.4127625637 6940842.0433131075
NOTICE:  Ring Self-intersection at or near point 2497617.4059891542 7004481.5565984966
NOTICE:  Ring Self-intersection at or near point 2483918.2574999928 6975295.9455000013 0
NOTICE:  Ring Self-intersection at or near point 2479238.1964862673 6956274.0468138419
NOTICE:  Self-intersection at or near point 2430764.1748952293 7027762.7618717942
NOTICE:  Ring Self-intersection at or near point 2508302.6419177013 7006131.0566753978
SELECT 183826

Query returned successfully in 1 min.

For some reason, only 183862 records were returned, when there are actually a total of 665518.

  • 1
    I suspect it is because you are excludiing invalid geometries. Perhaps try running ST_MakeValid on your geometries first and then re-run . – John Powell Dec 5 '16 at 7:53
  • What would be the command for that and can it be included in the code above? Would I have to Update the table first? – the_darkside Dec 5 '16 at 8:30
  • UPDATE parcel2016 SET geom = ST_MakeValid(geom) WHERE ST_IsValid(geom) = 'f'' and repreat for the other table. You could include ST_MakeValid directly in the query, but I always think it is better to clean geometries separately in an update, so you know what you are dealing with. – John Powell Dec 5 '16 at 8:36
  • thanks. I am returned: ERROR: Column has M dimension but geometry does not NOTICE: Ring Self-intersection at or near point 2500677.5199999958 6921843.0100000054 0 Query returned successfully in 2 min. – the_darkside Dec 5 '16 at 8:52
  • See this question about that error. If you now only have one invalid geometry, you might retry your original query to see if you get complete results now? – John Powell Dec 5 '16 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.