22

I am a long-time programmer, new to GIS. I am trying to get a feel for the database aspects of GIS and I understand that there are some databases which are specialized for GIS use. Basically I am trying to understand whether to use such a database or to stick with the hugely popular, well established, widely supported (and free) MySql.

The sort of applications I am liable to code would things like:

  • fleet management (land or ocean based vehicles)
  • employee tracking
  • inventory control (at a granularity of a physical position accurate to with a metre or so)
  • errm, that's about it, really

I would normally expect to be tracking no more than 1 few hundred (maximum, possibly a few thousand) items. Sometimes the items will be located in the same (large) building, series of building, city, country or world-wide, depending on the application.

I will occasionally be visually representing them either on a custom floor plan of a building or, more likely, in Google Earth or the like (more of which in another question).

It seems to me that I am as well off with MySql and adding columns for lat/long or other positional data, but that might just be because I know MySql.

Is there any reason why I should be looking at a more specialized database?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Mawg says reinstate Monica
  • 537
  • 1
  • 6
  • 19

3 Answers3

14

The real advantage to spatial databases (PostGIS, spatial extensions to MySQL or anything else) is that you can do spatial operations on spatial data. If you are just storing point coordinates, then you don't really gain much from spatial (just use two numerical columns). If you store combinations of point coordinates (where the customers are), and line coordinates (where the delivery trucks go), and polygons (sales regions), and then do queries that relate those various bits of information (how many customers within a sales zone, how many trucks traveled less than 2km from a customer site today but did not make a delivery or pickup), you can gain a lot.

As pointed out by iant, PostGIS is certainly worth a look for a server application. SpatiaLite is the spatial extensions to SQLite, which may be a better fit for a desktop / embedded / offline mobile application, while offering fairly similar SQL functions [Disclosure: I work on SpatiaLite], noting that there is nothing that stops you using PostGIS in a desktop application.

BradHards
  • 12,881
  • 2
  • 37
  • 70
  • +1 and thanks. Just curios as to why it was based on Sqlite and not MySql ... – Mawg says reinstate Monica Sep 29 '12 at 01:55
  • 1
    I think its just different design goals (embedded, no configuration, etc). – BradHards Sep 29 '12 at 03:03
  • +1 that was what I thought (I come from an embedded background too). Thanks for the info. I expect that I will end up with browser based apps (most of us will, even if we would prefer not to), so the "lite" is not so necessary. Just cursious, what sort of apps now where the d/b is in the embedded system/device? (sorry, still learning) – Mawg says reinstate Monica Sep 30 '12 at 10:36
  • 1
    embedded is a big area, but offline mobile seems to be an emerging area. – BradHards Sep 30 '12 at 22:31
  • 2
    @Mawg Most modern phones and browsers use sqlite as the datastore. It really is one single c file, and the API is super simple and it runs inprocess. Contrast that with MySQL which requires multiple processes running and a client/server architecture. Two very different uses cases – Ragi Yaser Burhum Oct 02 '12 at 22:46
  • Tx for bringing SpatiaLite to my attention, I use sqlite extensively, I didn't know it existed. – Glenn Plas Jan 14 '13 at 12:52
  • I would like to add that spatial reference system handling is also very good feature in PostGIS, far as i know MySQL and MSSQL spatial dont do that in database. Also if plan is to display data using WMS/WFS etc. using geoserver and/or Openlayers it will be much easier to visualise data from spatial database than relational. – simpleuser001 Oct 09 '14 at 07:37
8

You should certainly consider the hugely popular, well established, widely supported (and free) PostGIS. It will do every thing that MySQL can do and handle spatial locations as first class objects. Thus you can carry out selections based on points with in a bounding box (or other polygon) without having to write out all the comparisons etc.

Once you start to need to ask questions like which objects are in which country (or building) then PostGIS really comes into it's own and should you decide that you need to map your objects then all the popular open source tools will talk to PostGIS out of the box.

Ian Turton
  • 81,417
  • 6
  • 84
  • 185
  • +1 and many thanks. I am off to look into it now. Form a coders point of view I want to see hos well it talks to PHP if I go web based, and if I go Widnwos desktop app then I am using AnyDac database access components for Delphi, so I will email AnyDac support. Thanks again! – Mawg says reinstate Monica Sep 27 '12 at 08:29
  • 1
    To Mawg: I still have to see any of the 3rd party delphi database libs to support any of the GIS functionality in postgis, db2, spatiallite, sql server etc. Seems like that is something you have to do on your own. – Uffe Kousgaard Sep 27 '12 at 11:40
  • +1 @UffeKousgaard I only found this at $295 for the cheapest http://cartovcl.com/ – Mawg says reinstate Monica Oct 04 '12 at 01:52
  • 1
    Cartovcl (which I have been using for almost 10 years) is a flat file GIS SDK which has the GIS functionality built-in. It is not a thin API on top of a spatially enabled remote database. – Uffe Kousgaard Oct 04 '12 at 06:13
  • =1 Oic; I am new to GIS. I just stunmbled across thhis page but have not yet had time to read it http://delphi.about.com/od/toppicks/tp/aatpmapchart.htm – Mawg says reinstate Monica Oct 04 '12 at 06:34
  • 1
    Historical document :-) But TatukGIS is probably one of the most comprehensive all-round GIS SDK's out there (but also a bit expensive). But it is in the same league as cartovcl, i.e. gis-functionality is built-in, it is not an API on top of a remote database. – Uffe Kousgaard Oct 04 '12 at 09:20
  • +1 Thanks very much, @UffeKousgaard. Well, it looks like I will have to bite the bullet & buy CartoVcl - unless I decide to go browser based. – Mawg says reinstate Monica Oct 06 '12 at 13:39
2

To add to the answers and reiterate some points, one uses a spatially-enabled database if you have queries that relate to the spatial relations of the data, a few of which are the following:

  1. which points are within x kilometers of my points of interest
  2. which points are nearer
  3. how far a point is from other point
  4. which points are x kilometers within the access road

If such queries relating to WHERE is a significant feature for an application, then usually it strongly recommended to use a spatial database.

Of course, one can resort to coding / application logic for some of this queries, such as using the distance formula for 1, but one does not need to reinvent the wheel.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
arcsum
  • 73
  • 8