I'm developing a geospatial db for a company that operates at a number of large facilities. Its primary purpose is to help staff locate themselves and equipment within each facilities' multi-storey buildings. I'm using ArcGIS's Building Interior Space Data Model (BISDM) as a schema template and implementing it in PostGIS. The table structure looks something like this:
facility
building
floor
boundary
interior_space
equipment
transport
road
pedestrian_path
perimeter
fence
vehicle_gate
pedestrian_gate
Each parent table has a one-to-many relationship with it's children, so the schema defines a simple hierarchy. Each table has short_name
, long_name
and geom
columns (as well as others, depending on the table).
My problem is this: I want to build a reverse-geocoder API that takes lat/long as input and returns a string representing the address of the matching locations. I've gotten stuck on how to construct this string.
My idea was to create a query that takes as its starting point the row containing the geom closest to the lat/long point and then traverse up the table hierarchy, collecting the value of short_name for each parent record until it reaches facility
. So the assembled address in this case might be 'Room 323A, Floor 4, Building 12, Facility Q' (interior_space->floor->building->facility
). However, because I don't know which table my query will start from (since it could be a point anywhere within the facility) I don't know what the parent table names will be in advance, and so I can't construct any JOIN
queries.
I'm familiar with approaches like adjacency lists and closure tables to model hierarchal systems, but these all require the data to be in a single table. The PostGIS requirement (well, strong recommendation) for different geometry types to be kept in separate tables means I can't use these approaches.
Can anyone suggest how I can construct this string without having to create a bunch of hard-coded SQL queries?
road->town->city->state->country->world
? Try to use the BBOX aproach, get the geom property of each one and use to get the next level. Like:In wich floor is this space? Ah, this. In wich building is this floor? ...
and so on.