Spatial SQL - GIS Succinctly - page 55

Chapter 4 Spatial SQL

I've mentioned this breed of SQL many times in this topic, and to some extent I've also
described it in a brief fashion.

Spatial SQL is not anything special; if you take "Spatial" out of the name, it's just regular
SQL. Either way you're dealing with binary large objects, or blobs.

Just like working with images embedded in the database, these blobs have a special
meaning when processed by code that understands what they contain. It's the addition of
this code along with the extra SQL functions needed to use it that makes a spatially enabled
database.

In the following section, I'm not going to cover every possible permutation and function call.
At last count there are more than 300 independent functions in the OGC specification,
covering just about every possible scenario from spatial distance relationships, to
constructions of complex geometries, to clipping rasters for predefined vector paths.

Instead, using the data we've placed in our database, I'll guide you through some simple but
common operations—the type that anyone writing a GIS-enabled application is likely to use.

Before we discuss these operations, let's take a quick look at the input and output stages
described in the first chapter.

Creating and Retrieving Geometry

Even though we've already imported some data into our database, anyone writing a GIS app
also needs to be able to create geometry in the database, especially if the application is
going to allow editing.

Most geometry creation is performed in one of three formats:

 Well-known text (WKT)

 Extended well-known text (EWKT)

 Well-known binary (WKB)

We'll be using WKT in the examples that follow, and we'll only be performing the operations
in SQL without inserting any data into our database. EWKT is slightly different, mostly due to
the fact that in the textual representation of the geometry, the SRID is separated from the
rest of the data by a semicolon.

I prefer to use the WKT standard and the spatial function ST_SetSrid to set the SRID for my
geometries.

If you are using MS SQL, you may have to use EWKT since it doesn't have a ST_SetSrid
function. There is, however, a writable SRID property on the Geometry SQL type.