MySQL GIS: Overview and Tools

1
MySQL GISOverview and Tools
Jeremy Cole
jcole_at_yahoo-inc.com
2
Why me?!

• Im a map geek
• Im a hobbyist GIS user
• I love databases - I collect them
• I love creating tools to visualize data
• Yahoo! is not using GIS in any major way, but in
  a few limited places. Dont get any grand ideas!
  )

3
What is GIS?

• Geographic Information Systems - but what the
  heck does that mean?
• Different things to different people!
• A few basics Whatever youre doing has to do
  with maps in a general sense. There are lots
  of types of maps out there. There are lots of
  types of objects that make up a map.

4
Terminology / Buzzwords

• Geometry - A single shape
• MBR - Minimum Bounding Rectangle
• WKB - Well-Known Binary
• WKT - Well-Known Text
• Shapefile - ESRI-created format for GIS data
  (very very popular)

5
Types of things

• Vector-based features - points, lines, polygons
• Raster-based data - digital elevation models
  (DEMs), ground cover imagery, etc.
• Networks - Connecting lines together to form
  routes/paths between points
• Geocoding information - Mappings of addresses,
  zip codes, and things to points/shapes on maps

6
Where do I get data?

• There is a ton of free GIS data out there. Some
  sources are
• NationalAtlas.gov
• Atlas.gc.ca
• Census.gov
• Many countries, states/provinces, counties, and
  cities provide free GIS data - search for gis
  data for some ideas

7
OK, sounds cool!
8
MySQL Features

• Data types POINT, LINESTRING, POLYGON,
  GEOMETRY, MULTIPOINT, MULTILINESTRING,
  MULTIPOLYGON, GEOMETRYCOLLECTION
• Data format WKB, WKT
• Index type SPATIAL index type uses R-tree
  indexes to support geometries
• Functions creating, comparing, composing,
  decomposing geometries

9
Data types

• POINT A single point
• POINT(x y)
• LINESTRING A string of points
• LINESTRING( (x1 y1) (xN yN) )
• POLYGON A string of points that forms a closed
  shape by having its first and last point equal
• POLYGON( (x1 y1) (xN yN), (x1 y1) )
• Some additional rules apply here
• GEOMETRY A generic type for any type of GIS
  object

10
Internal stuff

• Internally, MySQL stores spatial data in WKB,
  Well-Known Binary, format
• Youll need to convert between text and binary
  format all the time

11
Creating geometries

• GeomFromText() is used to convert geometries from
  text to MySQLs internal representation
• AsText() is used to convert from MySQLs internal
  representation into readable text
• mysql select astext(geomfromtext("point(1 1)"))
• ------------------------------------
• astext(geomfromtext("point(1 1)"))
• ------------------------------------
• POINT(1 1)
• ------------------------------------

12
Great, but who types all that?

• So the next logical step is to somehow get some
  data loaded in semi-automatically
• libmygis is a great way to do that!
• jcole.us/software/libmygis
• Demo time!

13
libmygis
14
An easy way to get data in

• libmygis supports parsing a few interesting data
  formats
• ESRI Shapefile - reading
• WKB - reading/writing
• WKT - writing
• TIGER - some reading
• E00 - someone please save my sanity and finish it

15
On Shapefiles

• A Shapefile normally consists of three files
  really
• SHP - Shapes themselves
• SHX - Shape index
• DBF - Database fields in dBase III format
• Huge horrible mess of endianness issues!
• Records are in the same order in SHP, SHX, and
  DBF, there are no keys to match them up.
• libmygis should read them perfectly - its pretty
  well tested by now.

16
Stuff MySQL doesnt do
17
Non-MBR relationships

• OpenGIS defines many functions, such as
  Contains() which MySQL does not implement.
• Instead, MySQL maps Contains to MBRContains,
  which is far less useful.
• Eventually this should get done, but it might be
  a while.

18
(No Transcript)