Traversing a PostGIS table tree

Question

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?

Answer 1

Maybe this code point you in the correct direction: Supose you already get the_geom column and name from your interior space, you can apply this in a sub-query or view or function...

The 0.03 is the distance tolerance, so I need the closer the possible (you can use the FIRST 1 sql clause too)

select floor.name, floor.the_geom from floor_table floor where 
ST_DWithin(floor.the_geom, interior.the_geom, 0.03)

This will give you all floors wich the interior is within.

So, for each floor found ( expect only one, or the first one ), you can use it's the_geom to find the building in same way.

ETD : Presents you with a working code.

select 
    ( select 'COUNTRY:'||m.nome from mundo m where ST_DWithin(m.the_geom, c.the_geom, 0.03) ) as path1,
    'CITY:'||c.nome as path2, ' UNIT:'||u.serial as path3
from 
    units u, cidades c
where 
    ST_DWithin(c.the_geom, u.the_geom, 0.03) and u.gid = 34

In my example (sorry about the portuguese names), the UNITS is your interior, CIDADES (cities) is your floor and MUNDO (world) is your building. units->cidades->mundo

+-----------------+-----------------------+-----------------+
! path1           ! path2                 ! path3           !
+-----------------+-----------------------+-----------------+
! COUNTRY:BRAZIL  ! CITY: RIO DE JANEIRO  ! UNIT: MyCar001  !
!                 !                       !                 !
+-----------------+-----------------------+-----------------+

Hope this help you.

Answer 2

Drill down search from largest to smallest where all point are encompassed by the greatest spatial fence and all subsets are contained with a set above and incorporating all subsets below. The top domain could be 'world' and the smallest 'point' Then create a table at each level with all objects at that level and that has all associations linking 'up' and 'down'. Create a searchable index of each table with the correct linking and placing any point will link it back up to the world through the appropriate branches of the tree association. Try Flex, Javascript, GeoJSON.