0

What is the best way to store/expose locations?

Things to consider:

  1. I want to expose only locations from a specific area to user
  2. I want to be able to let user pick more or less direct locations (ex. suburb or city)
  3. It has to be future proof, let's say the area of available locations will expand

Are there more things to consider I haven't thought about?

The way I was thinking of doing it is to store location names as a tree structure.

Australia -> NSW -> Sydney -> Surry Hills -> 2011

Example picture: enter image description here

The end result doesn't have to be exactly as example picture. Also maybe I should not store locations at all? I was thinking to import them as well into a system from a geoJSON file.

Use case example

Let's say we have companies and the map of locations where they provide services. If the user is looking for companies in a suburb I'd like to show him the ones that provide services in the whole city as well (parent location).

  • They all look like strings to me. Why are you trying to store them as something else? What is that getting you? – candied_orange Nov 8 '17 at 1:16
  • @CandiedOrange I've added use case example – vardius Nov 8 '17 at 1:48
  • There are many overlapping questions on DBA.SE. In short: addresses are hard; they vary a lot between regions. Aus Post is quite consistent, however. Your plan will likely work well enough here. – Michael Green Nov 8 '17 at 1:49
  • 1
    @MichaelGreen Not only addresses. But any kind of geographic data. Especially if you want to build relationships between them. – Euphoric Nov 8 '17 at 5:03
  • So you do not see any other better solution ? Or more things that i should consider when designing location based service ? – vardius Nov 8 '17 at 5:04
4

Convert all you strings into actual latitudes and longitudes using some geocoding service.

Store coordinates of locations in a database that supports Geo distance queries (PostGIS, Elastic search)

Read up on how to query using geo relevance.

  • PostGIS KNN
  • ElasticSearch Geodistance

You might also want to look into Nested Sets but if you hierarchy has variable depth you may want to look into a graph database to have proficient queries.

  • If i'd like to select the whole city I would have to mark all inner locations as elected. Or how do i do that ? – vardius Nov 9 '17 at 4:58
  • 3
    You could pick a central point and an acceptable radius? – richzilla Nov 9 '17 at 9:45
  • @Vardius, you can do a radius search, or an intersection search if you store the actual geometries in the database. The GIS query capabilities are pretty flexible. Also, you can set up the hierarchy so that the city is a parent of all the child elements. That allows you to select all of them quickly. There's plenty of options. – Berin Loritsch Nov 9 '17 at 14:41
2

Since your use case shows the importance of administrative boundaries, physical distances as described by latitude and longitude are rather irrelevant. A business on the other side of a national border just 1 mile away could be of no use, in contrast to a business 10 miles away on the same side of the border.

You'll have to define the place hierarchy. A starting point may be the "admin_level" concept used by OpenStreetMap (see e.g. http://wiki.openstreetmap.org/wiki/Tag:boundary%3Dadministrative ). Note that not every country uses all levels.

But then the main issue for you will be filling the tables with the data, or rather getting the data from somewhere.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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