RE: Storing co-ordinate / spatial information in non-GIS systems

If 1 through 360,000,000 represent microdegree longitudes, and 1,000,000,000 through 360,000,000,000,000,000 (counting by billions) represent microdegree latitudes, then you get a single numeric with a maximum value under 3.7 x 10^17 easily handled by Oracle that you can easily look at in thousands separator demarked integers and trivially see the lat and long.

You can use 0 through 359,999,999 equally well.

That handles uniqueness constraints, and you can write check constraints to do the math that, for example, 1,360,000,001 is not a place.

How you handle unknown coordinates is more problematic. If you use 1...360, then you could imply unknown lat with 0 and unknown lon with less than 1 x10^9. (And you get to deal with all the attendant math handling to see if you know lat or lon all the time threaded throughout all your code.)

A more informationally rich solution is each point gets two booleans and a value. Pick any type you want for the booleans. One solution is a numeric with values 0,1,2,3 representing don't know either, know lat only, know lon only, know both. For more readable SQL code then separate columns Lat_known, Lon_known, position_value are better. Of course you can project those with a view, but you might want to use them naturally for selection without function indexes.

Likewise on projecting lat and lon. Of course you could check uniqueness on the composite number and keep lat and lon computed with check constraints so you can index them directly. Then if you so choose you could overload unknown on the separate lat and lon values as "NULL."

I'm not sure all that meets your requirements, and it is just an example of checking composite numbers by using disjoint ranges. It's pretty unlikely that you'll need more precision than microdegrees, which if you use a nominal 25,000 miles as the circumference are 4.4 inches each. (Don't actually use that number, that is just a rough edge scaling.)

If you need more precision:

1. Use bigger numbers
2. Don't plan on separating your points with GPS.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
Sent: Monday, August 15, 2005 2:41 AM
To: oracle-l
Subject: Storing co-ordinate / spatial information in non-GIS systems

Hi all,

I'm currently playing with storing simplistic geospatial information, and am interested in what experience others have of this. I'll note now that this is *simplistic* ... in no way am I approaching the need of a GIS system or the like. In essence, I'd like to store information like co-ordinate pairs (lat, long), a zoom level, etc. for interfacing with services like Google Earth, Google Maps, etc. The data would be transformed by xslt or similar to the requisite target format (keyhole markup, google maps javascript), so I don't need to store any of that baggage.

What I am grappling with is how to keep the co-ordinate pair "atomic". If I used separate fields for the latitude and longitude, an unknown point would have two nulls, a known point two values ... one of each would be "verboten", and hard to protect against using normal check constraints (or maybe I'm just having a senior moment). Obviously a numeric field won't store two values ... stuffing them into a varchar2 or the like, with some arbitrary delimiter, is a bit of a kludge. An object is appealing, but I'd prefer a purely relational solution.

Other dbs have implemented coordinate datatypes (PostgreSQL for instance) - someone might have done a port and had a bright idea.

So, anyone out there done this before, or have any suggestions?

Ciao
Fuzzy
:-)
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 15 2005 - 07:28:02 CDT