On Fri, February 1, 2008 3:55 am, Paul Scott wrote:
>
> Did anyone actually get this mail?
>
> More concrete example? What would you like to see?
>
> I suspect that some of my mail is getting dropped :(
I got the email below, and it made perfect sense...
Though I am not sure it would make sense to somebody who hasn't used
GIS before, or at least read about it...
Ultimately, what you do is have a NEW datatype to play with called a
'geometry' or a 'gis' or whatever the SQL guys chose to call it.
You also have ways to create "points" or "lines" or "polygons" etc,
which are sub-types of geometries.
And, finally, you can put an index on one of these nifty 'geometry'
columns, and it uses a btree to sort the data based on rules that make
sense for the underlying 'geometry' data.
For a concrete example, you might do something like this:
create table store (
store_id int(11) autoincrement unique not null primary key,
address text,
city varchar(255),
state char(2),
postal varchar(15),
longlat gis
);
create index store_gis_index on store(longloat);
inset into store(city, state, postal, longlat)
values ('Chicago', 'IL', '60601', GisLongLat(41.885844, -87.618128));
As far as you're concerned, it just goes really fast by "magic" when
you try to look something up by being "close to" a given LongLat.
Somewhere down in the guts of the DB, there is something to order the
data by both long and lat in a btree to find things quickly that are
"near" each other.
This is actually pretty OT for the PHP list itself, really, unless you
want to try to implement GIS in PHP, which would be a particularly Bad
Idea (tm) due to the scale, scope, and calculations involved.
Perhaps a Postgres GIS list or the MySQL list would be of more use, or
a Geography/Cartography database list.
You could probably achieve a SIMILAR performance level for something
as simple as long lat with:
create table store (
/* same columns as before */
long float,
lat float
);
create index store_long_lat_index on store(long, lat);
It wouldn't be QUITE as efficient, perhaps, as the ordering of the
btree for a GIS column might put more weight on the "lat" part
somehow, rather than just plain sorting by long, and then by lat...
But you have to have an AWFULLY large index for that to matter, since
the depth of the btree the same size, and only for specific clumped
data will it make any difference, I think.
Disclaimer:
I am NOT an expert on this stuff, but I have messed with it a tiny
bit, and this is my best understanding from my research so far.
>
> --Paul
>
> On Fri, 2008-02-01 at 06:33 +0200, Paul Scott wrote:
>> On Fri, 2008-02-01 at 03:40 +0100, Jochem Maas wrote:
>>
>> > I for one would really like to see a concrete example of this kind
>> of
>> > use of geometry columns and spacial indexes as an alternative to
>> the stand
>> > integer based primary keys.
>>
>>
>> On one of my local postGIS tables:
>>
>> CREATE INDEX k1
>> ON kanagawa
>> USING gist
>> (the_geom);
>>
>>
>> A gist index is a GEOS based spatial index. You will need GEOS to
>> create
>> one.
>>
>> When loading spatial data, your geometry column looks like so:
>>
>> 01050000000100000001020000000C00000011ECE564CF7561404A8999CCDABC4140E5C0981ACE75614012901CD641BD4140603C8386BE756140E525611B40BD41405BF216D3BD756140151DC9E53FBD414054DC1A4DBD756140760B997A3FBD414012219BD1BC756140D20823E33EBD41407AB2884EBC7561400F2110243EBD41404571B4D0BB756140CC0C6A213DBD4140F707192ABB7561405DF2A1803CBD4140F0F11CA4BA756140C3D1B7413CBD4140E89CB2ADB97561406F046D233CBD414017D4B7CCA97561406D47AD7F39BD4140
>>
>> Which is WKB (Well Known Binary) data or WKT (Well Known Text) data.
>> The
>> gist index simply indexes this as opposed to the regular gid (which
>> you
>> still use btree indexes on anyways)
>>
>> --Paul
>>
>> All Email originating from UWC is covered by disclaimer
>> http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
> --
> ------------------------------------------------------------.
> | Chisimba PHP5 Framework - http://avoir.uwc.ac.za |
> :------------------------------------------------------------:
>
> All Email originating from UWC is covered by disclaimer
> http://www.uwc.ac.za/portal/public/portal_services/disclaimer.htm
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
