CODEWITHSUNDEEP
databasealgorithmwindows-phone-7geocoding
Cameron
Cameron

Reputation: 430

WP7 - storing geocoordinates in a database

I need to store a large number of geocoordiantes in a database in a single record. It is not possible to store geocoordinates in an sql database so at the moment I'm doing this:

geocoordinates-->string-->bytes-->database

The limitation on strings in the database is 8000 bytes so this quantity of data must be stored in byte format (the database thinks it's an image so there's no byte limit). I've truncated the geocoordinates to 6d.p (down from 15d.p) but the process is very, very slow.

Does anybody know how to improve the efficiency of this task?

Thanks!

Upvotes: 0

Views: 186

Answers (3)

River
River

Reputation: 1514

Postgres does have direct support for points and other geometric primitives: http://www.postgresql.org/docs/9.1/interactive/datatype-geometric.html

Also, it has the <-> distance comparison operator that you might find useful http://www.postgresql.org/docs/9.1/interactive/functions-geometry.html

It is also possible to optimise indexes for nearest neighbour searches, which you can read about here: http://www.postgresql.org/docs/9.1/interactive/indexes-types.html

And finally, here is an example, taken from the above link:

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

There are dedicated GIS databases that you might want to look in to as well. One built on top of postgres is PostGIS: http://postgis.refractions.net/

Upvotes: 1

Zdeněk Topič
Zdeněk Topič

Reputation: 780

I would suggest you to store geolocation data into new table in latitude-longitude pairs with relation to row/record in your current table.

I dont know what are your plans with that data, but this seems like logical solution to me.

Upvotes: 1

Rowland Shaw
Rowland Shaw

Reputation: 38130

Why not redesign your database, and store the points as a pair of doubles to represent lat/long - these can all reference the "parent" row, if required.

Upvotes: 2

Related Questions