Skip to main content

Loading spatial data into MySQL

To be able to create choropleth maps in geobrowsers you'll need polygon data for the geographical units you want to map. These polygons can then be coloured according to a statistical value.

If you want to store geographic features in an open source database, you have two options: PostGIS/PostgreSQL or MySQL Spatial. MySQL has limited GIS support compared to PostGIS, but I want to test if it's sufficient for thematic mapping.

I will recommend two software packages: XAMPP and FWTools. XAMPP is an easy to install Apache distribution containing MySQL, PHP and PERL. FWTools is an open source GIS binary containing MapServer, GDAL/OGR, Proj.4, OpenEV, OGDI and Phyton. Maybe more than you need, but it is an easy install. The Swiss Army Knife is the GDAL/OGR library.

I have my boundary polygons stored in a shapefile. There is no MySQL command which enables you to upload spatial data to a database table (this can be done with PostGIS using the shp2pqsql command) . If you have installed the GDAL/OGR library, you can use the ogr2ogr command to upload point, lines or polygons from a shapefile. The command I used was:

ogr2ogr -f MySQL MySQL:my_database,user=root,password=mypassword TM_WORLD_BORDERS_SIMPL-0.1.shp -nln my_table -nlt MULTIPOLYGON -update -overwrite -lco GEOMETRY_NAME=my_polygon_column
Labels:

Comments

Unknown said…
Hi:

Thanks for leaving the example of how to import shapefile data to a mysql table. I'm working on doing the same sort of thing with some moderately large shapefiles and trying to integrate with google maps api - not sure if this approach is going to work terribly well or if I will have to go back to mapserver but my partner that does the GIS and archive work wants to give it a shot.

Thanks again,
Mike
13 April 2008 at 06:29
Anonymous said…
Found your blog by chance!
Really interesting themes here - its stored now in my feedreader

thanks

Kurt
28 May 2008 at 10:31
Autodesk said…
you can also populate a MySQL database using AutoCAD Map 3D. This desktop GIS uses FDO (Feature Data Objects) an open source technology that allows you to 'bulk copy' data from one datastore (i.e. Shapefile) to another (i.e. MySQL)
19 July 2008 at 19:26
Zaharia Constantin said…
Hello, i have download TM_WORLD_BORDERS-0.3 and imported inside mysql, but some countries are missing, like Moldova, Bulgaria. Is possibile to update also with this counties?
26 November 2013 at 20:07
Anonymous said…
Hi,

Thanks for the post. I've succeded exporting shp file to mysql using ogr2ogr. The polygon is stored in blob in geometry column. Using asText function i realized that the point inside the polygon is not WGS84 point (Country:Malaysia). How to correct it?
Example : POLYGON((-1537.2089012992 24645.590501093,-1673.2760986513 24610.21210087 .......)

Thank for yr help.
26 March 2014 at 02:04
Post a Comment

Popular posts from this blog

Creating a WebGL Earth with three.js

This blog post will show you how to create a WebGL Earth with three.js , a great JavaScript library which helps you to go 3D in the browser. I was surprised how easy it seemed when reading a blog post  by Jerome Etienne . So I decided to give it a try using earth textures  from one of my favourite cartographers, Tom Patterson . WebGL is a JavaScript API for rendering interactive 3D graphics in modern web browsers without the use of plug-ins. Three.js is built on top of WebGL, and allows you to create complex 3D scenes with a few lines of JavaScript. If your browser supports WebGL you should see a rotating Earth below: [ Fullscreen ] To be able to display something with three.js, you need three things: a scene, a camera and a renderer. var width  = window.innerWidth,     height = window.innerHeight; var scene = new THREE.Scene(); var camera = new THREE.PerspectiveCamera(45, width / height, 0.01, 1000); camera.position.z = 1.5; var rende...
11 comments
Read more

Thematic Mapping Engine

It's time to introduce the Thematic Mapping Engine (TME). In my previous blog posts, I've shown various techniques of how geobrowsers can be used for thematic mapping. The goal has been to explore the possibilites and to make these techniques available to a wider audience. The Tematic Mapping Engine provides an easy-to-use web interface where you can create visually appealing maps on-the-fly. So far only prism maps are supported, but other thematic mapping techniques will be added in the upcoming weeks. The engine returns a KMZ file that you can open in Google Earth or download to your computer. My primary data source is UNdata . The above visualisation is generated by TME ( download KMZ ) and shows child mortaility in the world ( UNdata ). The Thematic Mapping Engine is also an example of what you can achieve with open source tools and datasets in the public domain: A world border dataset is loaded into a MySQL database . The same database contains tables with statistics ...
45 comments
Read more

Creating 3D terrains with Cesium

Previously, I’ve used three.js to create 3D terrain maps in the browser ( 1 , 2 , 3 , 4 , 5 , 6 ). It worked great for smaller areas, but three.js doesn’t have built-in support for tiling and advanced LOD algorithms needed to render large terrains. So I decided to take Cesium for a spin. Cesium is a JavaScript library for creating 3D globes and 2D maps in the browser without a plugin. Like three.js, it uses WebGL for hardware-accelerated graphics. Cesium allows you to add your own terrain data, and this blog post will show you how. Impressed by the terrain rendering in @CesiumJS - with a 10m elevation model for Norway! Farewell Google Earth. pic.twitter.com/RQKvfu2hBb — Bjørn Sandvik (@thematicmapping) October 4, 2014 Compared to  the dying Google Earth plugin , it's quite complicated to get started with Cesium. The source code is well documented and the live coding Sandcastle is great, but there is a lack of tutorials  and my development slows down when ...
12 comments
Read more