Записки программиста, обо всем и ни о чем. Но, наверное, больше профессионального.

2013-03-25

PostgreSQL Geography

Намедни загружал я шейп-файлы в БД PostgreSQL. По ходу узнал много нового :)
Например, в PostGIS есть тип данных Geography, помимо Geometry

The geography type provides native support for spatial features represented on "geographic" coordinates (sometimes called "geodetic" coordinates, or "lat/lon", or "lon/lat"). Geographic coordinates are spherical coordinates expressed in angular units (degrees).

The basis for the PostGIS geometry type is a plane. The shortest path between two points on the plane is a straight line. That means calculations on geometries (areas, distances, lengths, intersections, etc) can be calculated using cartesian mathematics and straight line vectors.

The basis for the PostGIS geographic type is a sphere. The shortest path between two points on the sphere is a great circle arc. That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics. For more accurate measurements, the calculations must take the actual spheroidal shape of the world into account, and the mathematics becomes very complicated indeed.


The conclusion?

If your data is geographically compact (contained within a state, county or city), use the geometry type with a cartesian projection that makes sense with your data. See the http://spatialreference.org site and type in the name of your region for a selection of possible reference systems.

If, on the other hand, you need to measure distance with a dataset that is geographically dispersed (covering much of the world), use the geography type. The application complexity you save by working in geography will offset any performance issues. And, casting to geometry can offset most functionality limitations.


В двух словах: для огромных территорий, где кривизна глобуса начинает значительно влиять на точность измерений, лучше применять Geography тип данных, ибо система координат исходит из сфероида. Для малых территорий, где погрешность вычислений из-за кривизны глобуса невелика, можно применять планарные СК, тип двнных Geometry.

Кроме того, заявлено, что стандартный тип Geometry автоматом приводится к типу Geography если SRID=4326.

Интересно, когда уже дистанцию начнут считать по эллипсоиду?

Сниппет — как загрузить шейпы в БД PostGIS (updated 2013-04-01)
set path=%path%;c:\Program Files\PostgreSQL\9.0\bin
pushd c:\t\p
chcp 1251

psql -e postgisdb postgres

CREATE ROLE giswriter NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE USER mfs WITH password '12345678' IN ROLE giswriter;
CREATE SCHEMA mfsdata AUTHORIZATION mfs;
GRANT ALL ON SCHEMA mfsdata TO mfs;
ALTER USER mfs SET search_path TO mfsdata,public;

revoke mfs from giswriter;
grant giswriter to mfs;
GRANT SELECT ON geometry_columns TO giswriter;
GRANT SELECT ON geography_columns TO giswriter;
GRANT SELECT ON spatial_ref_sys TO giswriter;
commit;
\q

shp2pgsql.exe -c -I -G -W cp1251 patching.shp mfsdata.patching > patching.dump.sql
psql -f patching.dump.sql -e postgisdb mfs

И сцылки по теме

original post http://vasnake.blogspot.com/2013/03/postgresql-geography.html

Комментариев нет:

Отправить комментарий

Архив блога

Ярлыки

linux (241) python (191) citation (186) web-develop (170) gov.ru (159) video (124) бытовуха (115) sysadm (100) GIS (97) Zope(Plone) (88) бурчалки (84) Book (83) programming (82) грабли (77) Fun (76) development (73) windsurfing (72) Microsoft (64) hiload (62) internet provider (57) opensource (57) security (57) опыт (55) movie (52) Wisdom (51) ML (47) driving (45) hardware (45) language (45) money (42) JS (41) curse (40) bigdata (39) DBMS (38) ArcGIS (34) history (31) PDA (30) howto (30) holyday (29) Google (27) Oracle (27) tourism (27) virtbox (27) health (26) vacation (24) AI (23) Autodesk (23) SQL (23) humor (23) Java (22) knowledge (22) translate (20) CSS (19) cheatsheet (19) hack (19) Apache (16) Manager (15) web-browser (15) Никонов (15) Klaipeda (14) functional programming (14) happiness (14) music (14) todo (14) PHP (13) course (13) scala (13) weapon (13) HTTP. Apache (12) SSH (12) frameworks (12) hero (12) im (12) settings (12) HTML (11) SciTE (11) USA (11) crypto (11) game (11) map (11) HTTPD (9) ODF (9) Photo (9) купи/продай (9) benchmark (8) documentation (8) 3D (7) CS (7) DNS (7) NoSQL (7) cloud (7) django (7) gun (7) matroska (7) telephony (7) Microsoft Office (6) VCS (6) bluetooth (6) pidgin (6) proxy (6) Donald Knuth (5) ETL (5) NVIDIA (5) Palanga (5) REST (5) bash (5) flash (5) keyboard (5) price (5) samba (5) CGI (4) LISP (4) RoR (4) cache (4) car (4) display (4) holywar (4) nginx (4) pistol (4) spark (4) xml (4) Лебедев (4) IDE (3) IE8 (3) J2EE (3) NTFS (3) RDP (3) holiday (3) mount (3) Гоблин (3) кухня (3) урюк (3) AMQP (2) ERP (2) IE7 (2) NAS (2) Naudoc (2) PDF (2) address (2) air (2) british (2) coffee (2) fitness (2) font (2) ftp (2) fuckup (2) messaging (2) notify (2) sharepoint (2) ssl/tls (2) stardict (2) tests (2) tunnel (2) udev (2) APT (1) CRUD (1) Canyonlands (1) Cyprus (1) DVDShrink (1) Jabber (1) K9Copy (1) Matlab (1) Portugal (1) VBA (1) WD My Book (1) autoit (1) bike (1) cannabis (1) chat (1) concurrent (1) dbf (1) ext4 (1) idioten (1) join (1) krusader (1) license (1) life (1) migration (1) mindmap (1) navitel (1) pneumatic weapon (1) quiz (1) regexp (1) robot (1) science (1) serialization (1) spatial (1) tie (1) vim (1) Науру (1) крысы (1) налоги (1) пианино (1)