1

I have a 9x9 grid and a polygon of municipalities of an extensive area of Brazil. My goal is to have a field indicating the biggest municipality per grid cell.

I've been trying with intercept without good results. I know there are similar questions but I can't figure it out how to handle it in QGIS.

Here is a screenshot of what I'm talking about:

enter image description here

I was thinking that maybe with intersection and then spatial union joining with maximum but it didn't work.

I want to get the final grid with a field indicating the biggest municipality inside each grid cell.

1

Here's another way of doing it, I couldn't take my mind off of this thing and at the office I made some time to solve this; these are the steps I took in Kosmo:

1.- Create the grid 10x10 km and concatenate Row and Column into a key field

2.- Intersect the grid with the map of municipalities the new map is called IntGridMun

3.- Recalculate AREA for IntGridMun

4.- Sort the shapefile IntGridMun using sortshp (FWTools) "sortshp IntGridMun IntGridMunAREADesc AREA descending" this will create a new shapefile IntGridMunAREADesc

5.- Generate stats for IntGridMunAREADesc grouping by Row and Column key field and obtaining the First municipality name

6.- Join the stats table with the original grid and you should have your map

enter image description here

Hope this helps

Juan José Del Toro

  • Thank you so much @jdeltoro. I'm gonna try and write the results. – Sergio Sep 17 '13 at 23:07
  • Hi @jdeltoro, please, clarify this to me: My grid has the following fields: ID, XMIN, XMAX, YMIN, YMAX. In the Calculadora option i created a new field called [KeyField] (sergioibarra.blogspot.com.br/2013/09/…) CONCAT([ID],[XMIN],[XMAX],[YMIN],[YMAX]) Is this what you are talking about? Thanks – Sergio Sep 18 '13 at 0:21
0

Here's a possible database solution. If you import both the municipalities and the grid polygons into Spatialite, for example, and each polygon spatial table has an "id" column, then a query like this:

SELECT m.municipal_id
FROM muinicpalities AS m JOIN grid AS g ON ST_Intersects(m.geometry, g.geometry)
GROUP BY g.grid_id
ORDER BY ST_AREA(ST_Intersection(m.geometry, g.geometry) DESC LIMIT 1;

should give you the id of the municipality with the largest intersection within each grid cell. Now you can use that to update the grid table as follows:

ALTER TABLE grid ADD COLUMN municipal_id INTEGER;

UPDATE grid SET municipal_id=(
SELECT m.municipal_id
FROM municpalities AS m JOIN grid AS g ON ST_Intersects(m.geometry, g.geometry)
WHERE g.grid_id=grid.grid_id
ORDER BY ST_AREA(ST_Intersection(m.geometry, g.geometry) DESC LIMIT 1;
);

HTH

  • Hey @Micha, here is my table of attributes. I tried with Spatialite wihtout any results. I think i'm doing something wrong: The polygon with the municipalities is:BRA_adm2 The grid polygon is:grid9x9latlon8 The id of the municipality is: ID_2 So, would be the code like this? SELECT m.ID_2 FROM BRA_adm2 AS m JOIN grid9x9latlon8 AS g ON ST_Intersects(m.geometry, g.geometry) GROUP BY g.grid_id ORDER BY ST_AREA(ST_Intersection(m.geometry, g.geometry) DESC LIMIT 1; When i run i got errors Thank – Sergio Sep 11 '13 at 16:48
  • What is the id column in your grid table called? What errors do you get? – Micha Sep 12 '13 at 9:08
  • the id column of the grid is called "ID". When i ran the script i got the following message: SQL error: near "DESC": syntax error – Sergio Sep 14 '13 at 22:17
  • Ooops, I see I missed a closing parenthesis: ...ORDER BY ST_AREA(ST_Intersection(m.geometry, g.geometry)) DESC ... – Micha Sep 15 '13 at 5:44
  • Thank you so much @Micha, the municipality with the largest intersection within each grid cell is ID_2 = 5128 Then i ran ALTER TABLE grid9x9latlon8 ADD COLUMN ID_2 INTEGER; It was ok, and then: UPDATE grid9x9latlon8 SET ID_2=( SELECT m.ID_2 FROM BRA_adm2 AS m JOIN grid9x9latlon8 AS g ON ST_Intersects(m.geometry, g.geometry) WHERE g.ID=g.ID ORDER BY ST_AREA(ST_Intersection(m.geometry, g.geometry)) DESC LIMIT 1 ); And now i'm waiting for the results, is it the code fine? I'm sorry for my basics questions, I'm new in spatialite and SQL programming. The good thing is that I'm gonna study it – Sergio Sep 17 '13 at 23:05

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.