What RDBMS do you use when doing location-aware things?
What RDBMS do you use when doing location-aware things?
More specifically, I am looking into a simple app with use of GPS.
All I need to do is:
- Get a location
- Verify if there is a nearby location in the DB
- Show some information if there is
I am trying to use PostGis but it's really awful to use.
What does everyone else use?
QF, I am thinking of just going with longitude and latitude as integers by multiplying by 1M.
Opinions?
More specifically, I am looking into a simple app with use of GPS.
All I need to do is:
- Get a location
- Verify if there is a nearby location in the DB
- Show some information if there is
I am trying to use PostGis but it's really awful to use.
What does everyone else use?
QF, I am thinking of just going with longitude and latitude as integers by multiplying by 1M.
Opinions?
By "simple app" you're talking about Android- or Apple-smartphones? Both have SQLite on-board, so you may use that for storing your POIs.
ReplyDeleteYou've got that wrong. :) PostGIS is awesome. Personal taste perhaps.
ReplyDeleteFor calculating distances accurately you need to take the curvature of the earth in count. PostGIS will abstract those details away for you. It can reproject on the fly, use spatial indexes, etc.
In a database, don't store your coordinates as integers. Store them as geometric type "point". It'll allow for smarter indexing and GIS operations.
If all you want is to calculate the distance to a set of points (like: show nearby shops), then any database will do. In a crappy db like MySQL you'll need to create a great circle distance function that takes two points and returns the distance (you can find it on the web, or ask me and I'll send one). Call that in all of your points. If it's slow because you have many points, first check if points are in a certain box before you call the more expensive distance function on them.
If you're serious with GIS, and you don't want to spend the price of a car on Oracle, then PostGIS is your only option, really. Do yourself a favour and give it a fair chance.
SQLite does not meet my definition of RDBMS. Plus, there will be a back-end service. What I am looking for is a simple-ish way to find locations near the current point. Also, tutorials on geolocation, what users generally expect, etc. would be most welcome.
ReplyDeleteWouter van Nifterick, I would quite happily but it seems to me that it's really complex. For example: how do you create a column representing a point? I can't find any easy way to do that. I have added the PostGIS extension plus other extensions but I can't find a column type that seems to do what I want.
ReplyDeleteWouter van Nifterick and also: what's the business with tiger and tiger_data? Why do you need two schemas? And I don't know which tables have records after the import of shape files. I have googled around a bit but this seems to be information you must already know, which is really not handy.
ReplyDeleteAndrea Raimondi you can add a geometry column, and store points in there, or you can use postgis, which will take care of some additional stuff.
ReplyDeleteSomething like `select AddGeometryColumn('myschema', 'mytable', 4326, 'POINT', 2)`
You've mentioned latitude/longitude, so I assume you're working with wgs84 or gps locations. 4326 is the code for that projection.
Note that what you want can also be done with any other database. Or if you have less than a few hundred locations, you might as well fetch the data and implement this outside of the database...
Wouter van Nifterick, right now my scope is this:
ReplyDelete- Get point (easy with the location component)
- Assign it to a column in the database (uh... I can't define a geography column in my tables... what am I missing?)
- Get new point
- Check if there is any other point in the DB nearby the current one
SQL Server provides a geography type, but I would rather not go with something which may turn out to cost lots of money if the application is successful.
David Heffernan except that some some databases are better at dealing with geometry than others. MySQL is notoriously stupid in this area. It always assumes that the earth is flat.
ReplyDeleteOf course, if this functionality is just a small part of your system then it doesn't make sense to let GIS support dictate the choice of technology.
Wouter van Nifterick, this is little more than an experiment but focused on GIS (location awareness, etc.). The issue here is: I want to make sure that I am using the right things :) PostgreSQL seems pretty neat, it even lets you "derive" tables and with the use of foreign tables you could in theory have other data stores as well (I am pretty certain that you can connect other RDBMses, too).
ReplyDeleteIf you haven't seen this already, this link:
ReplyDeletegeospatialworld.net - Spatial database services for location-aware applications - Geospatial World
Suggests Oracle’s Spatial, Informix 2D Datablade, and IBM’s DB2. Personally, I'd probably store the location information as at least 2 floats (latitude, longitude), and maybe a 3rd for altitude, and find some SQL and/or code (most likely in PHP) to do the calculations. On the other hand, I'm guessing that Google probably has such a service
/sub
ReplyDelete/interested
ReplyDeleteDavid Nottage if your rdbms supports a geometry or even a point type, don't store floats. Please take that advice from me.
ReplyDeleteIt's as silly as storing floats as two integers or as text: operators and functions won't work with them, so the first thing you'd always have to do is convert to the right type. That means you can't properly index using r-trees, searches will be slow, and you'll have to store information like the projection separately.
If you want to obtain lat or lon individually you can always read them from your points.
Sqlite3 with RTree virtual table extension, as compiled with our framework. Optimized for coordinates search.
ReplyDelete/sub
ReplyDeleteA. Bouchez, SQLIte does not fit my simple definition for RDBMS. No types and no consistency, how can people use it in production for anything even mildly complex is beyond me. SQLite must die.
ReplyDeleteAndrea Raimondi Sqlite is quite useful as a local storage and query engine. As a replacement for something like Postgres it's worthless. This is how it's explained on the sqlite homepage.
ReplyDelete"SQLite does not compete with client/server databases. SQLite competes with fopen()."
We use MSSQL for this kind of tasks. Set up and querying is really easy.
ReplyDeleteAnyway, look at this for MySQL, which is the most logical free rdbms if you don't like Postgres. stackoverflow.com - MySQL Great Circle Distance (Haversine formula)
ReplyDeleteThe top solution is a bit brute force. It calculates the distance to ALL points, so expect this to be slow if you have a lot of points (thousands or millions). It's much faster to first check if points are within a certain box, or to even pre-calculate certain distances.
Andrea Raimondi I am afraid you are plain wrong about Sqlite3. It is one of the safest, most standard, most tested and the most spread rdbms worldwide - you can trust it. It is not sqlite2!
ReplyDeleteIf you are planning to store and retrieve poi's go with MySql. If you are planning to build maps go with Postgree. I think that MySql is better choice for you, because you only wish to get poi's from a position to a distance provided by you. You can find poi's using a simple query like this:
ReplyDeleteSELECT `id`, ( 6371 * acos( cos( radians( :lat ) ) * cos( radians( `lat` ) ) * cos( radians( `long` ) - radians( :long ) ) + sin(radians(:lat)) * sin(radians(`lat`)) ) ) as` distance` FROM `location` HAVING `distance` < :distance ORDER BY `distance` LIMIT 25
Where :lat is your latitude, :long is you longitute and :distance is the maximum distance in kilometers fom the position. If you wish to calculate in miles, replace 6371 by 3959.
Wouldn't you need to embed MySQL if you want to use it locally on Android? And wouldn't you need a commercial license if you did that? https://www.xaprb.com/blog/2009/02/17/when-are-you-required-to-have-a-commercial-mysql-license/ Or was the original question strictly for client/server databases?
ReplyDeleteRik van Kekem I believe the op will not store the data (poi's) on the client side, but on a server side, so no license is required. Anyway, MariaDB does the same.
ReplyDeleteIndeed. This is a service with a mobile front-end but I always tend to be an all-or-nothing kind of guy: if we're storing data outside of the app, then all data needs to be outside, otherwise all inside. Makes no sense to have half and half :)
ReplyDeleteAndrea Raimondi In modern REST applications, you don't start from tables and SQL (like with a 2-tier/RAD design), but you define objects, and use a persistence service. Then you can freely cache the objects on the client side, especially for offline mode. On that side, you don't need a SQL engine: local storage (even HTML5 key/value store) is enough. On the server side, you don't expose directly the DB, but define an application service with ready-to-use objects, then a persistence service using the chosen storage (SQL or NoSQL) - persistence agnosticism is a good idea nowadays: it allows to mock the store, for better testing, for instance.
ReplyDeleteA. Bouchez, I am sure you know (see what I did here?) that REST isn't always the best option. REST is one of the options, but not the only one. More specifically, I find that the Code First approach that RemObjects provides gives me the kind of flexibility I need while keeping things very much safe on both ends. I think I am, overall, more productive if I don't have to fight with JSON malformed content, etc. Plus, the service is really tiny and also "dried up" for now. It may expand but it's really for later.
ReplyDeleteYeah. Exposing MySQL to the big bad internet isn't really a good idea, so if you're really going to directly access a server make sure you have good security in place (limit ip, vpn etc.). But I assume you already thought of that.
ReplyDeleteRik van Kekem thankfully, I am not. I only need to know what RDBMS works best to store and manage that kind of data.
ReplyDeleteAndrea Raimondi You are right: manual REST can be PITA. But you can use code-first approach over REST: for instance, you define interface-based services with WCF or our mORMot. The sooner you have a clean interface-oriented service, the better - even for a tiny service (and services should always have a small focused scope, if you follow SOLID principles).
ReplyDeleteA. Bouchez, one of the main reasons driving me away from mORMot is your continued plugging and pushing even in situations where it is clearly not appropriate. Please stop.
ReplyDeleteIf SQlite3 RTREE is not enough - https://sqlite.org/rtree.html - you may try https://www.gaia-gis.it/fossil/libspatialite/index which is mostly OGC-SFS compliant.
ReplyDeleteIt's still lacking types => no go. I flat out refuse to work with type-less stuff unless there is a compellingly good reason, which in this case there is not.
ReplyDeleteDo you wish a tutorial? This is a simple and good one: fromdual.com - MySQL spatial functionality - points of interest around me
ReplyDeleteAndrea Raimondi SQlite3 is not "lacking types", but has "columns type affinity" -
ReplyDeletesee https://sqlite.org/datatype3.html
Type checks should be done at class level, with real string typing, not at SQL. For instance, it is up to the persistence layer (with an ORM or not) to work properly with object field types, and convert them to the underlying DB engine typing system. The "affinity" used by SQLite3 is IMHO a better path than inconsistent type definitions of most other databases (who needs a maximum column length for text in the 21th century? - at least PostgreSQL favors unlimited TEXT type).
Andrea Raimondi This is not about the tooling, but about the design: I mentioned also WCF. Using interfaces is always appropriate, even for the simplest use cases. From my experiment, I don't see any serious/professional business project done in .Net since years without interface abstraction, and unit testing. Even the simplest class do expose an interface.
ReplyDeletePostgreSQL + PostGIS only ;-)
ReplyDelete