Geographic DatabasesGeographic databases have special data types for geographic and geometric data. In addition, they have internal operations to compute the distances between geometric objects (points, line segments, and polygons) on a 2-D plane or a 3-D space (geometrically) and on the approximately spherical surface of the earth (geographically). SQL queries on geographic data can be made to run much faster by constructing geographic indexes.
Unfortunately, the internal support for geospatial data storage and comparison operations varies greatly from database to database. Alpha Five Version 11 introduces a portable syntax for working with geographic data that is supported for those databases that have sufficient internal support for the core data types and operations.
What good is geographic data?In a world where most cellphones can determine their location, geography is important for any task that depends on where the user is physically. For example, the user may want to find a nearby restaurant — searching the web for all restaurants anywhere in the world would be absurd. Conceptually, what a location-dependent restaurant finder application might want to do is:
- Get the current latitude and longitude from the phone hardware
- Send a query to a restaurant database to find restaurants within X miles of the current location
- display the options to the user on the phone
- Get the address of the business being visited
- Convert the address to latitude and longitude
- Send a query to a hotel database to find hotels within X miles of the specified location
- display the options to the user on whatever device or computer he is using
Geographic data nomenclatureWe have already mentioned latitude, longitude, points, line segments, and polygons. If any of those terms are unfamiliar you will have difficulty following the rest of the discussion.
A standard for geospatial systems is managed by the Open Geospatial Consortium (OCG). Unfortunately, every current implementation of OCG in a database differs from the standard in some way.
Geographic data may be represented as Well-known-text (WKT), for example
POLYGON((100 200, 110 300, 120 500))
The geospatial data needs to be defined as belonging to a reference system. Reference systems are indicated by Spatial reference identifiers (SRIDs). Unfortunately, there are many different SRIDs. The default SRID for most databases is 4326. The default for DB2 is 1003. In most cases, the default will suffice; therefore, the SRID argument to all Portable SQL geography functions is optional. (See http://en.wikipedia.org/wiki/SRID for additional information on SRIDs.)
Geographic indexes need to be created in the database to make geographic queries run fast (or, in the case of Oracle, to run at all). The instructions for doing this vary with the database.
Databases supported for geographic dataAt release time, Alpha Five Version 11 supports geographic data in Microsoft SQL Server, Oracle, DB2, PostgreSQL and (to some extent) MySQL.
How do I create a table with geographic data?See Common Geography Database Tasks V11 and the database-specific task notes for your database, Microsoft SQL Server, Oracle, DB2, PostgreSQL or MySQL.
Once you have created the table with a Geography field, don't forget to set up a geographic index on the field, which will make searching for a location within a circle or polygon much faster. (On Oracle, this is a requirement, not an optional step.) Unfortunately, this is a complicated database- and data-dependent task that we're unable to document in a general way. You should refer to your database documentation; in a pinch, you might want to discuss your requirements with a database administrator who has training on or experience with geographic indexes for your brand of database.
- Changes to SQL Objects for Geometry V11
- Portable SQL Functions for Geographies V11
- Common Geography Database Tasks V11
- Database-specific Geography Tasks V11
- SQL Geography Examples V11
- How to bulk geocode addresses in a database V11
- Working with SqlTypes V11