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
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
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?