Programming for cross-database deployment

The main mechanism for implementing object-relational querying is, of course, SQL. As such it is the principle element through which we should aim to use for maximal cross-database querying. In this section, we will first look at querying the basic geometry types, and then finish by putting all we have learned together to implement something of greater use in day-to-day spatial processing.

Querying ST_POINT geometries

The querying of points is quite simple:

– SQL Server 2012
Select a.geom.STX as x, a.geom.STY as y
  From (Select geometry::STPointFromText('POINT(301301.0 5201201.0)',28355) 
                 as geom) a;

-- PostgreSQL
Select ST_X(a.geom) as x, ST_Y(a.geom) as y
  From (Select ST_PointFromText('POINT(301301.0 5201201.0)',28355)
                   as geom) a;

-- Oracle ST 
Select a.geom.ST_X() as x, a.geom.ST_Y() as y
  From (Select book.ST_Point('POINT(301301.0 5201201.0)',28355) 
                   as geom 
          From dual) a;
-...