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

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <Grant.Allen@xxxxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 15 Aug 2005 07:20:01 -0400

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
:-)
--
//www.freelists.org/webpage/oracle-l


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

Other related posts: