3

As I am unfamiliar with any tools I decided to try my luck with PostGis. I imported my data. and executed the query :

create merged as
select delomrade_code, st_union(geometry)
from grunnkrets
group by delomrade_code

it seems to be doing a much better job on my data. first it executed in minutes not in hours. and the result set has only 6 MULTIPOLYGONS out of 1546 as oppose to over 200 I got with the Spatialite.

I'm OK with this result and will try to dig deeper and understand why I couldn't get these 6 areas as I wanted.


I'm new to GIS. I have seen relevant and possibly duplicate questions but can't solve this problem.

I'm using Spatialite. I have a table with the basic statistical area units of norway (Grunnkrets) - 13990 polygons. I added a column to the table named "delomrade_code" with the code of groups of these smaller polygons - 2224 unique values.

I'm trying to convert the these 13990 polygons to 2224 bigger polygons containing these smaller polygons.

first I tried running the query :

select delomrade_code, GUnion(Geometry) as geometry
from Grunnkretser
group by delomrade_code

The result geometry column has mostly polygons (success) but a few hundred lines contain MULTIPOLYGONS. I need the result to be polygons only. what do I need to do in order to try to avoid the multipolygons in the result ?

I suspect there is some minor difference in LatLng of point in adjoining polygons and therefore GUnion fails to merge these polygons. I then tried running :

select delomrade_code, GUnion(Buffer(Geometry, 0.02)) as resulting_polygon
from grunnkretser
group by delomrade_code

results were better, more of the 2224 rows in the result were polygons but still not all.

The documentation of the Buffer function says :

return a geometric object defined by buffering a distance d around geom, where dist is in the distance units for the Spatial Reference of geom

How do I find my :

...distance units for the Spatial Reference of geom

  • The spatial reference depends on how you imported it. You need to tell us that. For the second question, it isn't clear what you are asking, so we can't say whether this is the right direction if you don't tell us what you are actually trying to achieve. – BradHards Aug 5 '14 at 9:23
  • @BradHards Obviously the aim is to get one polygon per "delomrade". Buffering may not be the best thing to do because it will mean that the resulting delomrade polygons will overlap. I would try to correct the topology first with SnapToGrid function so that there would be no overlaps nor gaps between the original "grunngretser" polygons. – user30184 Aug 5 '14 at 11:07
  • Is that really the aim? More likely there is some future intent for those polygons, and knowing that future intent might change the nature of this step. – BradHards Aug 5 '14 at 11:14
  • Regarding the way I import the data, I get a SOSI file from norgeskart.no then I converted the file to KML using newconverter.mygeodata.eu. then converted the KML to SHP using the same service. I would like to remind my newbeness and that I just go with what I find. – bodman Aug 6 '14 at 15:45
  • The aim is certainly to get the polygons for the "delomrade". I would like to use these polygons later as a separate layer. – bodman Aug 6 '14 at 16:33
2

If you converted the data to KML, and didn't change the coordinate reference system when you went from KML to SHP, then the units are almost certainly in degrees.

Let me say that again - your distance measure is now degrees. It is not metres, miles or kilometres.

Degrees probably isn't what you wanted for a buffer. So you may want to project (using the SpatiaLite transform() function) into a coordinate system that uses something like metres. See Create Buffer in meters Spatialite query for an equivalent question.

In terms of an efficient query, you probably want to use the spatial index to speed up the test. There is a closely related problem in the spatialite documentation, and the union example may also help. So you would create a bounding rectangle for each polygon you have, then look for any rectangle which intersects any other bounding rectangle. Then test only those for potential merge.

  • thank you @BradHards, I finally used PostGis and got much better results. – bodman Aug 8 '14 at 16:15
  • postgis provides the spatial index automatically, which might explain the difference. Perhaps you could post your solution as an answer to the question so it can be closed out? – BradHards Aug 9 '14 at 0:29
0

As @BradHards suggested I'm posting my solution:

  • I installed PostGis,
  • Imported the data,
  • and executed the following query:

    create merged as
    delomrade_code, st_union(geometry)
    from grunnkrets
    group by delomrade_code
    

results were much better with my data. first it executed in minutes. and the result set has only 6 MULTIPOLYGONS out of 1546 as oppose to over 200 I got with the Spatialite.

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.