[OSM-dev] help with PostGIS and/or PostgreSQL queries?

[Rich Morin]

# Background

The Muddy Map Explorer will support text-based exploration and navigation
of map data, making it accessible to blind and visually disabled users.  The
user interface will be patterned after MUDs (Multi-User Domain games), REPLs
(Read, Eval, Print Loops), etc.  It will let users explore a mapped area at
various scales, getting descriptions of streets, amenities, etc.

Using osm2pgsql, I've set up an exploratory database containing pretty much
every possible relation and column, based on Geofabrik data on Texas and
California (where my collaborator and I, respectively, live).  So, I've got
a nice pile of data to play with.  However, I'm still a newbie at this, so
I need help in developing some database queries.


# Finding Nearby Ways

Given a geographic location, it should be possible to find nearby ways, using
only PostGIS and PostgreSQL.  This would help us to get a working set of ways
(and indirectly, nodes) for the user to explore.

I found a few notions that seem tantalizingly close, but no exact matches:

- Find nearest neighbours faster in PostGIS
  https://gis.stackexchange.com/questions/63667/find-nearest-neighbours-faster-in-postgis

- How Do I Find The N Nearest Things To This Point In PostGis
  https://boundlessgeo.com/2011/09/indexed-nearest-neighbour-search-in-postgis

- PostGIS / OSM: Faster query to find nearest line of points
  https://gis.stackexchange.com/questions/207592/postgis-osm-faster-query-to-find-nearest-line-of-points

- Spatial Relationships and Measurements
  http://postgis.refractions.net/documentation/manual-svn/ST_DWithin.html

Q: Is there an efficient way to find nearby ways for a location, using just
   PostgreSQL and/or PostGIS?


# Tracking cities, etc.

We'd like to be able to tell the user the current location, in human-friendly
terms.  Although we may know the starting location, traveling even a short
distance could put the user into a different city, county, or country.

We can't pester a geocoding service each time the user moves (and don't want
to install and maintain Nominatim if we don't have to), so we need a local
solution.

A brute-force approach would be to look for nearby nodes that have TIGER
(or equivalent) attributes.  If the current location is "close enough" to a
known address, that may be sufficient to our needs.

Q: Is there an efficient way to get the administrative region for a location,
   using just PostgreSQL and/or PostGIS?

-r

 -- 
http://www.cfcl.com/rdm           Rich Morin           rdm at cfcl.com
http://www.cfcl.com/rdm/resume    San Bruno, CA, USA   +1 650-873-7841

Software system design, development, and documentation