4

I have an Excel sheet with grid data including geographical coordinates. I know that the coordinates represent areas, i.e., a multi(polygon). However, the data lacks geometry information. I'm looking for an open source way of converting these coordinates into the corresponding geometries, mainly polygons and points. I'm hoping that I can do this using QGIS. I've looked around for information on how to achieve this, but no luck so far.

I'm not even sure this is possible. I'm still researching, but any idea is welcome.

UPDATE: I know that for a polygon, there are supposed to be at least 3 coordinates. But these are just given as (x,y). Nothing that conforms to a linear ring, for instance.I'm thinking that the coordinates in my Excel sheet represent the center of the polygon. Is there away of telling what the polygon is. This sounds far fetched, perhaps because I've never done anything like this before. Your opinions will be highly appreciated.

UPDATE: Sample Data enter image description here

I plan to separate the 'Geolocation' column into x and y columns. Having thought about it for sometime, I think it's impossible. But any idea that might help will be appreciated.

2
  • 1
    Can you paste a sample of what you have. Currently, it doesn't seem feasible.
    – underdark
    Aug 22, 2012 at 14:01
  • I've updated the question to include some sample data, @underdark.
    – okello
    Aug 22, 2012 at 17:11

3 Answers 3

2

You can create a Voronoi diagram. That's as good as it gets I'm afraid.

The function is available in QGIS vector menu.

5
  • I've done a little research on that, and it sounds like the only option I've got for now. I guess I'll just do with roughly sketched polygons (Voronoi diagrams) for now, before I can clean them up. Thanks.
    – okello
    Aug 22, 2012 at 18:09
  • 1
    I guess you are dealing with administrative borders in Kenya. You can install Openlayers plugin from Sourcepole repository, and take Openstreetmap as background. They have some detailled borders inside Kenya.
    – AndreJ
    Aug 22, 2012 at 18:50
  • You are right, @Andre. I've been looking for OpenStreetMap extracts for counties in Kenya, but no success yet, as the ones I've found have very limited information. I liked the Cloudmap's cleaned up shapefiles, but Cloudmade website appears to have issues from my location.
    – okello
    Aug 23, 2012 at 6:15
  • The borders in OSM are taken from this site: maplibrary.org/stacks/Africa/Kenya/index.php. The ESRI Shape file format (unzipped) can be read directly by Qgis. I don't know if it is detailled enough for you.
    – AndreJ
    Aug 23, 2012 at 6:35
  • @Andre, I've downloaded the admin one, and loaded it to QGIS. It has fewer details that I need, but its a good starting point. Thanks.
    – okello
    Aug 23, 2012 at 7:01
2

Breaking up the geolocation field is easy with Excel text functions. Assuming your data is in field I1, insert in the following fields as follows:

J1: =Left(I1;FIND(",";I1)-1)

K1: =Right(J1;Len(J1)-1)

L1: =Right(I1;Len(I1)-FIND(",";I1))

M1: =Left(L1;Len(L1)-1)

Not sure about the correct funcion names, I'm using a german Excel version.

The coordinates are in K1 and M1. Just copy down the formulas to all rows, then Copy and "Insert values". Then you can delete fields J and L.

1
  • @Andrea, Thanks. I've been wondering how I was going to do this, given there are over 50K records.
    – okello
    Aug 22, 2012 at 19:08
1

To add a x-y-table form Excel to qgis, you first have to save it as csv. Then you can import it with Layer-> Import Layer from text file. To convert the points to polygons or lines, you can use the points2one plugin from the old Qgis Contributed repository. You have to add it manually: http://pyqgis.org/repo/contributed.

Other way would be to draw the polygon manually with snapping to the existing points enabled.

The coordinates are those of the points (vertices) of the polygon, not the center. There is no way to get a polygon shape if you only have the center coordinates.

6
  • I understand that a polygon must have more than one point. But the data I have doesn't have that kind of info. It only includes coordinates that I assume are the center of the corresponding polygons. I was hoping that I don't have to manually draw the polygons. One thing you've mentioned that I think might help is with snapping to the existing points enabled. If you don't mind, can you please expound a little more on this.
    – okello
    Aug 22, 2012 at 17:15
  • I'm afraid this would not be very helpful to you. If you only have one point per polygon, you have to guess the area the polygon takes. I was assuming that you have all edge points of your polygons, and only have to connect them. Anyway, you can do the Import from text layer as mentioned above, and use the coordinates to label the data on a map.
    – AndreJ
    Aug 22, 2012 at 18:41
  • Thanks, @Andre. I have the area data, too. I've to figure out how to combine this with @underdark's suggestion on using Voronoi diagram.
    – okello
    Aug 22, 2012 at 18:49
  • If you have border and point, this might be helpful: multimedia.journalism.berkeley.edu/tutorials/…
    – AndreJ
    Aug 22, 2012 at 19:10
  • And how's the border supposed to be represented? Is it also represented using coordinates? Can polygon area play a role? I'm sorry if this question is stupid as I'm new to this.
    – okello
    Aug 22, 2012 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.