[postgis-users] Can we store physical locations using local coordinate system in postGIS?

[Nordgren, Bryce L -FS]

Paul's suggestion of using the ST_Affine function is likely going to work, but the biggest problem is likely to be the definition of transformation chains which go from your building's origin to the floor's origin to the room's origin to coordinates on particular objects. PostGIS doesn't understand transformation chains.. I think you will find this to be a common feature of any spatial database implementing the "Simple Features Specification for SQL". The crux of the problem is that the "spatial_ref_sys" table lacks columns to support hierarchical relationships, and doesn't define "chained" transformations as something which is required to be handled.

There is yet another factor: you may not want the quirks of various map projections messing with your building blueprints. The errors may not matter at the small scale of a building, but if your tolerances are pretty high, it might. If this is even potentially a problem, you may want to check out the SEDRIS Spatial Reference Model (SRM). [1] The "Local Tangent Space" coordinate system is a true 2D or 3D coordinate system (your choice of Cartesian or spherical cords, possibly cylindrical too) which is tied to geospatial locations via an origin on the surface of the earth...or Mars...or any of the terrestrial planets. This is a programming library rather than a database, but it does represent a mature, rigorously tested system which has implemented the basic concept you're after...And once you're done defining your building, the library supports launching it into orbit and calculating where to point your telescope. The main advantage is that it won't mess with your coordinates.

In either case, to store "hierarchical" information like this using the data types at hand, I suspect that you could do the following:

* Reserve a block of "SRIDs" which are not currently in the spatial_ref_sys table. Declare one of these to be the "building" coordinate system and remove it from the pool.
* Create a table called "floors" . The columns would include parameters for an affine transform. Another column would define which SRID applies to the floor, which is populated by values from the reserved block. You could potentially include a geometry column which could represent the origin of the floor, or something fancier if you want--the important part is that you store the affine parameters. If you include a geometry column, you must ensure that it uses the SRID you have declared to be the "building" coordinate system--not the floor one. Each row defines a "floor" coordinate system in terms of the building coordinate system.
* Create a table called "rooms". Same columns as above. In this case, entries in your (optional) geometry column will employ an SRID allocated in your "floors" table. The SRIDs defined by this table serve as the reference system for the rooms.
* Create a table called "objects". This table does not declare any SRIDs, and must have a geometry column. The SRIDs used by geometry entries in this table must be declared in either the floors or rooms tables, or must be the "building" SRID.

Your application logic is going to have to understand the above arrangement. If you want extra credit, and the ability to use ST_Transform, learn how to generate a "proj4" conversion string, assuming your blueprints can be expressed in one of the map projections it understands. You then create an entry for each of your defined SRIDs in the spatial_ref_sys table using this proj4 string. Furthermore, your application logic must maintain the spatial_ref_sys table by updating the proj4 string for all the "downstream" reference systems whenever you mess with something upstream e.g., when you move the building on the earth, or a floor within a building.

Alternatively, instead of affine transforms you could just use the above tables to store enough data to initialize skads of local tangent space coordinate systems, and have the client do all the spatial junk using the SEDRIS SRM linked to your app. This would allow you to use any old nonspatial database as a backend, but it would preclude selecting objects using spatial predicates.

Bryce

[1] http://www.sedris.org/srm_desc.htm





This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.