Jump to content

 
Photo

Counting neighboring areas

- - - - -

  • Please log in to reply
13 replies to this topic

#1
SerbanR

SerbanR

    Contributor

  • Validated Member
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Timisoara
  • Romania

Hello
I am working primarily with MapInfo 8.x, but I also have Manifold 7.x and FME installed.
I have a map containing all the administrative divisions of Germany (12.555 polygons, TAB format) and I need to create another column in my table containing the number of neighbors of each administrative division.
Does anyone knows how can I do that?

Thanks
Serban

#2
gregsd

gregsd

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 169 posts
  • Gender:Male
  • Location:Surrey, England.
  • Interests:Cycling, mostly off-road (MTB) riding.
  • United Kingdom

Hello
I am working primarily with MapInfo 8.x, but I also have Manifold 7.x and FME installed.
I have a map containing all the administrative divisions of Germany (12.555 polygons, TAB format) and I need to create another column in my table containing the number of neighbors of each administrative division.
Does anyone knows how can I do that?

Thanks
Serban


Serban,

In MapInfo you can do this with an SQL Query. First of all though you'll need to make a copy of your administrative divisions table, which you can delete later on. You can use the Query -> SQL Select dialog -

Select Columns: ID, Count(*)
From Tables: Admin_Divisions, Admin_Division2
Where Condition: Admin_Divisions.obj intersects Admin_Divisions2.obj AND
Admin_Divisions.ID <> Admin_Divisions2.ID
Group by Columns: 1
Into Table Named: Selection

In the above example the ID column is a name or code that is unique to each administative division and Admin_Divisions is your original table, Admin_Divisions2 the copy you created earlier.

This will produce a query which will list each administative division by ID and a count of the number of neighbouring divisions. You can then use this query to update your original table using the Table - Update Column dialog.

Hopefully, I've explained it clearly but if not, then let me know.

Regards

Greg.


Greg Driver

GIS Analyst
MapInfo User...!

#3
gregsd

gregsd

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 169 posts
  • Gender:Male
  • Location:Surrey, England.
  • Interests:Cycling, mostly off-road (MTB) riding.
  • United Kingdom

Oh, forgot to say that given the numbr of polygons you have to process it might take awhile :mellow:


Greg Driver

GIS Analyst
MapInfo User...!

#4
Hans van der Maarel

Hans van der Maarel

    CartoTalk Editor-in-Chief

  • Admin
  • PipPipPipPipPipPipPip
  • 3,898 posts
  • Gender:Male
  • Location:The Netherlands
  • Interests:Cartography, GIS, history, popular science, music.
  • Netherlands

I am working primarily with MapInfo 8.x, but I also have Manifold 7.x and FME installed.
I have a map containing all the administrative divisions of Germany (12.555 polygons, TAB format) and I need to create another column in my table containing the number of neighbors of each administrative division.
Does anyone knows how can I do that?


FME of course :) (oh come on, did anybody here think I'd say anything else :P )

If you use a SpatialRelator and have it check for "TOUCHES", it will report the number of neighbours. Just enter your original data in both the Base and Candidate ports, like this:

Attached File  fme.jpg   23.54KB   73 downloads

I did this for the 468 municipalities in The Netherlands and that ran in less than 7 seconds. The AttributeKeeper is to remove unnecessary attributes. An added benefit of the SpatialRelator is that it makes a list (of all the attributes) of the neighbouring polygons.

Hope this helps...
Hans van der Maarel - Cartotalk Editor
Red Geographics
Email: hans@redgeographics.com / Twitter: @redgeographics

#5
SerbanR

SerbanR

    Contributor

  • Validated Member
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Timisoara
  • Romania

Dear Hans

Thank you for your reply. I've used FME as you can see in the attached picture and it worked very well.
It took me around 30 min to figure out how to do it but in the end I was impressed with FME speed. The actual computation time was 6.30 minutes!.

Dear Greg

Thanks a lot for your help and suggestion on how to do this in MapInfo. You seem to be a real SQL master! I think that on 12000+ adm divisions the operation would be much more time consuming than using FME. Anyway, I really need to understand your query so I will try to apply it on a smaller example area and I'll come back with feedback.

Thank you both for your help!
BR
Serban

Attached Files



#6
SerbanR

SerbanR

    Contributor

  • Validated Member
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Timisoara
  • Romania

Thank you Greg for your answer!
It works very well, I've tested it with administrative boundaries for Romania (43 polygons) and it is ok (see attached picture).
I do not understand "Group by Columns: 1" part of the query, I just noticed that it does not work if this is not specified - if this group by is not specified then the query returns only one result instead of 43!.
Maybe you could explain this a bit.

Thanks a lot!
Serban

Attached Files



#7
SerbanR

SerbanR

    Contributor

  • Validated Member
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Timisoara
  • Romania

Strange behavior for MapInfo/SQL solution !

I tried the solution proposed by Greg on my 12.555 polygons table.
It took around 15 minutes to complete (as opposed to FME 6.5 min), and in the end the resulting table has 12537 entries instead of 12555!?!

Greg do you have any idea why this is happening ?

Serban

Attached Files



#8
gregsd

gregsd

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 169 posts
  • Gender:Male
  • Location:Surrey, England.
  • Interests:Cycling, mostly off-road (MTB) riding.
  • United Kingdom

Thank you Greg for your answer!
It works very well, I've tested it with administrative boundaries for Romania (43 polygons) and it is ok (see attached picture).
I do not understand "Group by Columns: 1" part of the query, I just noticed that it does not work if this is not specified - if this group by is not specified then the query returns only one result instead of 43!.
Maybe you could explain this a bit.

Thanks a lot!
Serban

Serban,

The group by clause is a way of grouping records with the same value and doing some sort of data aggregation, like a count or finding the average or sub-total of a column. The 1 relates to the first column in 'Select : Name, Count(*)" , so 1 = Name (you can put either 1 or the name of the column). In this example you're grouping by Name and counting how many objects are returned by the where condition part of the query for each unique Name.

Hope that helps.

Greg.


Greg Driver

GIS Analyst
MapInfo User...!

#9
gregsd

gregsd

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 169 posts
  • Gender:Male
  • Location:Surrey, England.
  • Interests:Cycling, mostly off-road (MTB) riding.
  • United Kingdom

Strange behavior for MapInfo/SQL solution !

I tried the solution proposed by Greg on my 12.555 polygons table.
It took around 15 minutes to complete (as opposed to FME 6.5 min), and in the end the resulting table has 12537 entries instead of 12555!?!

Greg do you have any idea why this is happening ?

Serban

Hmmm, not without seeing the table. My first thoughts would be that there may be records that don't have objects connected to them. You can check this using SQL -
Select * from tablename where not obj

I'd also do a check to see if there were any duplicate records as the group by clause will aggregate these -
Select Admin_Name, count(*) From Tablename Group by 1 Order By 2 DESC

This will give you a list of admin names and count of how many times they occur, ordered by the Count(*) column from high to low (DESC = descending), so if there any multiple instances then they'll be listed first.

I'm more than happy to have a look at the table if you're happy to send it too me?

Regards

Greg.


Greg Driver

GIS Analyst
MapInfo User...!

#10
SerbanR

SerbanR

    Contributor

  • Validated Member
  • PipPip
  • 15 posts
  • Gender:Male
  • Location:Timisoara
  • Romania

Thank you Greg for your answer!
It works very well, I've tested it with administrative boundaries for Romania (43 polygons) and it is ok (see attached picture).
I do not understand "Group by Columns: 1" part of the query, I just noticed that it does not work if this is not specified - if this group by is not specified then the query returns only one result instead of 43!.
Maybe you could explain this a bit.

Thanks a lot!
Serban

Serban,

The group by clause is a way of grouping records with the same value and doing some sort of data aggregation, like a count or finding the average or sub-total of a column. The 1 relates to the first column in 'Select : Name, Count(*)" , so 1 = Name (you can put either 1 or the name of the column). In this example you're grouping by Name and counting how many objects are returned by the where condition part of the query for each unique Name.

Hope that helps.

Greg.


Thanks Greg for your explanations.
Do you have any idea related to my other question (see this topic: http://www.cartotalk...showtopic=2574). Do you think it is possible to make that kind of statistics using MapInfo/Vertical Mapper ?

#11
gregsd

gregsd

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 169 posts
  • Gender:Male
  • Location:Surrey, England.
  • Interests:Cycling, mostly off-road (MTB) riding.
  • United Kingdom

Thanks Greg for your explanations.
Do you have any idea related to my other question (see this topic: http://www.cartotalk...showtopic=2574). Do you think it is possible to make that kind of statistics using MapInfo/Vertical Mapper ?

Hi,

I haven't really used VM that much, though I do have it on my PC and I thought there should be a way to achieve the results you wanted. Well, I had a play around today (didn't get much work done :ph34r: ) and I've come-up with a way to do it. I'll post something tomorrow whilst I'm at work as I can't quite remember the names of the functions in VM that I used to do it! It's not quite that straightforward though, as it does involve converting the .grc file into a polygon layer and using SQL to get the results.

Till tomorrow!

Greg.


Greg Driver

GIS Analyst
MapInfo User...!

#12
wari

wari

    Newbie

  • New Member
  • Pip
  • 2 posts
  • Estonia

Hey!

sry for digging up this old thread, but still:

I have the exact same wish as Serban,

but when I enter the described string in MapInfo "SQL Select" dialog, the "OK" and Verify" buttons stay inactive / gray.

Why is that?


Mihkel

Attached Files



#13
gregsd

gregsd

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 169 posts
  • Gender:Male
  • Location:Surrey, England.
  • Interests:Cycling, mostly off-road (MTB) riding.
  • United Kingdom

Hey!

sry for digging up this old thread, but still:

I have the exact same wish as Serban,

but when I enter the described string in MapInfo "SQL Select" dialog, the "OK" and Verify" buttons stay inactive / gray.

Why is that?

Mihkel


Mihkel,

I think the only reason that the buttons will be greyed out is that one of the tables listed in the SQL select dialog isn't valid. Presumably you have a saved a copy of your original table, omavalitsus as omavalitsus2 using File -> Save Copy As? If not, then do this and open the new table (omavalitsus2.tab) and then re-run the SQL select and see how you get on. The other possibility is that one of the table names is spelt incorrectly, so that's another thing to check.

Greg.


Greg Driver

GIS Analyst
MapInfo User...!

#14
wari

wari

    Newbie

  • New Member
  • Pip
  • 2 posts
  • Estonia

Thank you so much gregsd!


Both were the problem,
I didn't have both tables open,
and I had to use column name VNIMI instead of ID in the end of the sting in "where Condition"

Mihkel




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

-->