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

[Harshal Vora]

Nordgren, Bryce L -FS <bnordgren <at> fs.fed.us> writes:

> 
> 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 y
>  ou'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.
> 


Hi,

Thanks for the solutions. It makes a lot more sense now.

Consider that I do not want to convert to lat/lon. For my application,
lat/lon for the POI is enough i.e. the entire building can be represented by
just one lat/lon or a boundary. I can obtain this by reverse geo-coding the
building's address. For multiple buildings, I can then trace a path using
any map API.

>From what I understand, I can associate every node in the hierarchy i.e.
building/floor/room with their own coordinate system. To convert between
these coordinate systems, I need to generate the Affine matrix and perform
Affine transformation.

If I have the floor plan for the building, floors as well as individual
rooms then this should be achievable. But somehow I also need to represent
the hierarchical relationship between the different entities in the database.

If I can achieve the above two things, I can use any database as backend, I
can represent geometry components using WKT format and use GeoTools (or any
other library) to perform geometric operations in memory.

What do you think about using a graph database like neo4J to store the
hierarchy and somehow link every node to its geometry and coordinate system?
Application logic can take care of converting from one coordinate system to
another as we traverse the graph. 
The only reason to use a graph database is that it provides a better way of
representing and querying hierarchy.

Most of my queries are going to be like
1) Check if two rooms are on the same floor
2) Find all rooms adjoining a corridor area
3) Find which room a particular point belongs to (from a finite list of rooms)
4) Find least common ancestor in the hierarchy and the distance between two
nodes in a coordinate system represented by least common ancestor node if
present.  
5) I would like to represent every node using 3D and also perform affine
transformations using 3D. 

Can I explain you in further detail in an email?

Regards,