GIS In MySQL Database

------=_NextPart_000_0000_01C633E6.991F2E50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I have a Win32 MySQL database. I want to add GIS information, specifically
latitude and longitude. Reading the MySQL docs online, it seems like the WKT
format can be used to describe this information. If I understand the docs
correctly, it is stored in a blob. (I assume a TinyBlob.) If I wanted to add
the latitude and longitude of a location using SQL, how would I do that?

INSERT INTO tablename (longlat) VALUE('POINT(40.12345 -83.12345)')

where longlat would be a TINYBLOB.

Would that be correct or have I missed the train as it pulled out of the
station?

Thanks.

Mike




------=_NextPart_000_0000_01C633E6.991F2E50
Content-Type: text/plain; charset=us-ascii


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
------=_NextPart_000_0000_01C633E6.991F2E50--

Re: GIS In MySQL Database

----- Original Message -----
From: "Michael Avila"
To: "MySQL - Win32"
Sent: Friday, February 17, 2006 11:21 PM
Subject: GIS In MySQL Database


>I have a Win32 MySQL database. I want to add GIS information, specifically
> latitude and longitude. Reading the MySQL docs online, it seems like the
> WKT
> format can be used to describe this information. If I understand the docs
> correctly, it is stored in a blob. (I assume a TinyBlob.) If I wanted to
> add
> the latitude and longitude of a location using SQL, how would I do that?
>
> INSERT INTO tablename (longlat) VALUE('POINT(40.12345 -83.12345)')
>
> where longlat would be a TINYBLOB.
>
> Would that be correct or have I missed the train as it pulled out of the
> station?
>
Yes, this is correct but if the 'POINT(40.12345 -83.12345)' is basic string
you can store in char or varchar type filed too. Blob field is meant for
binary data mainly.

Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: GIS In MySQL Database

On Sat, 18 Feb 2006 04:09:29 +0100, you wrote (with possible editing):

>----- Original Message -----=20
>From: "Michael Avila"
>To: "MySQL - Win32"
>Sent: Friday, February 17, 2006 11:21 PM
>Subject: GIS In MySQL Database
>
>
>>I have a Win32 MySQL database. I want to add GIS information, =
specifically
>> latitude and longitude. Reading the MySQL docs online, it seems like =
the=20
>> WKT
>> format can be used to describe this information. If I understand the =
docs
>> correctly, it is stored in a blob. (I assume a TinyBlob.) If I wanted =
to=20
>> add
>> the latitude and longitude of a location using SQL, how would I do =
that?
>>
>> INSERT INTO tablename (longlat) VALUE('POINT(40.12345 -83.12345)')
>>
>> where longlat would be a TINYBLOB.
>>
>> Would that be correct or have I missed the train as it pulled out of =
the
>> station?
>>
>Yes, this is correct but if the 'POINT(40.12345 -83.12345)' is basic =
string=20
>you can store in char or varchar type filed too. Blob field is meant =
for=20
>binary data mainly.
>
>Petr Vileta, Czech republic
>(My server reject all messages from Yahoo and Hotmail. Send me your mail=
=20
>from another non-spammer site please.)

Wouldn't it be a more efficient use of disk space to store it as a
char or varchar than as a blob? Thanks,

--

Larry
Email to rapp at lmr dot com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: GIS In MySQL Database

> Wouldn't it be a more efficient use of disk space to store it as a
> char or varchar than as a blob? Thanks,
>
Maybe, if you have many rows. Varchar and Blob is variable length fields,
but Blob use string_length +2 bytes and Varchar use string_length +1 byte
only.

This say the MySQL manual:

6.2.6.3 Storage requirements for string types
Column type Storage required
CHAR(M) M bytes, 1 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 1 <= M <= 255
TINYBLOB, TINYTEXT L+1 bytes, where L < 2^8
BLOB, TEXT L+2 bytes, where L < 2^16
MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 2^24
LONGBLOB, LONGTEXT L+4 bytes, where L < 2^32
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of
enumeration values (65535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4 or 8 bytes, depending on the
number of set members (64 members maximum)

VARCHAR and the BLOB and TEXT types are variable-length types, for which the
storage requirements depend on the actual length of column values
(represented by L in the preceding table), rather than on the type's maximum
possible size. For example, a VARCHAR(10) column can hold a string with a
maximum length of 10 characters. The actual storage required is the length
of the string (L), plus 1 byte to record the length of the string. For the
string 'abcd', L is 4 and the storage requirement is 5 bytes.

The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of
the column value, depending on the maximum possible length of the type. See
section 6.2.3.2 The BLOB and TEXT Types.

Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)





--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: GIS In MySQL Database

------=_Part_17451_20889895.1142100755911
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi, folks -

Having worked with GIS for 37 years, I'd like to make a suggestion on this
subject:

Consider what you want to do with the data whenever you retrieve the
long/lat from the database. Do you want to find nominal distances between
points? Do you want to represent the data as DMS when giving the long/lat
for a place? Both?

If the nominal distance is required, I'd save the data as a float (possibly
double depending on the accuracy) because you're going to wind up having to
convert from DMS to decimal representations in order to do the proper
calculations. Might as well have 30.12345 (or whatever) already in a
decimal format.

If you just want to display the long/dat in some other context, for example=
,
location name followed by the long/lat, then representing it as a char
probably makes sense. Varchar in this situation doesn't make sense to me
*unless you have other varchars* because the number of bytes you'd save is
probably trivial. Might as well have them represented as a DMS string.

HTH.

virginia
--
\ / Virginia R. Hetrick, here in sunny California
0 Voicemail: 310.471.1766 Email: drjuice@gte.net
Oo "There is always hope."
Keeping track of the weather:
http://www.washington.edu/cambots/camera1_l.gif

------=_Part_17451_20889895.1142100755911--

Re: GIS In MySQL Database

I'm currently working on a project in which we are storing lat/long
values in our database of locations which is very substantial (over
375,000 unique locations) to calculate nominal distances and we're using
float values since the data is already in decimal format. We've found
that for nominal distance caluclation, we're saving time having to
convert values, and increasing efficiency since the data is already in a
decimal format.

As a side note, the only thing I would caution on is if you're using
some type of mapping software to determine lat/long values prior to
entering them in the database - mind you I'm in Canada so our zip code
system is somewhat bulky and complex. We're using MS MapPoint and have
noticed some inconsistencies with the values it returns. For instance,
we could have 2 unique locations at the same physical address; think a
building with multiple businesses. What I've seen is two different
values returned even though the only difference in the data being sent
is that the Country for one is "Canada" and the other is "CA". Something
to consider! Cheers.

Armando

Dr. Virginia R. Hetrick wrote:
> Hi, folks -
>
> Having worked with GIS for 37 years, I'd like to make a suggestion on this
> subject:
>
> Consider what you want to do with the data whenever you retrieve the
> long/lat from the database. Do you want to find nominal distances between
> points? Do you want to represent the data as DMS when giving the long/lat
> for a place? Both?
>
> If the nominal distance is required, I'd save the data as a float (possibly
> double depending on the accuracy) because you're going to wind up having to
> convert from DMS to decimal representations in order to do the proper
> calculations. Might as well have 30.12345 (or whatever) already in a
> decimal format.
>
> If you just want to display the long/dat in some other context, for example,
> location name followed by the long/lat, then representing it as a char
> probably makes sense. Varchar in this situation doesn't make sense to me
> *unless you have other varchars* because the number of bytes you'd save is
> probably trivial. Might as well have them represented as a DMS string.
>
> HTH.
>
> virginia
> --
> \ / Virginia R. Hetrick, here in sunny California
> 0 Voicemail: 310.471.1766 Email: drjuice@gte.net
> Oo "There is always hope."
> Keeping track of the weather:
> http://www.washington.edu/cambots/camera1_l.gif
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org