Ikhtisar
GanosBase Geometry merupakan ekstensi geometri spasial dari AnalyticDB for PostgreSQL. GanosBase Geometry mematuhi standar OpenGIS dan memungkinkan AnalyticDB for PostgreSQL untuk menyimpan dan mengelola data geometri spasial 2D (X, Y), 3D (X, Y, Z), dan 4D (X, Y, Z, M). GanosBase Geometry juga menyediakan berbagai fitur seperti objek geometri spasial, indeks, fungsi, dan operator. Model geometri ini sepenuhnya kompatibel dengan PostGIS dan memungkinkan Anda bermigrasi secara mulus dari aplikasi yang ada.
Panduan cepat
Instal ekstensi.
Untuk menginstal ekstensi
ganos_spatialrefdanganos_geometry, ajukan tiket.Buat tabel geometri.
Metode 1: Buat tabel yang berisi bidang geometri. CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25), geom geometry(LINESTRING,3857)) DISTRIBUTED BY (ID);Metode 2: Buat tabel standar dan tambahkan bidang geometri ke tabel CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25) ) DISTRIBUTED BY (ID); SELECT AddGeometryColumn( 'roads', 'geom', 3857, 'LINESTRING', 2);Tambahkan batasan geometri.
ALTER TABLE ROADS ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(geom));Impor data geometri.
INSERT INTO roads (id, geom, road_name) VALUES (1,ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',3857),'Jalan Lingkar Utara Kelima'); INSERT INTO roads (id, geom, road_name) VALUES (2,ST_GeomFromText('LINESTRING(189141 244158,189265 244817)',3857),'Jalan Lingkar Timur Kelima'); INSERT INTO roads (id, geom, road_name) VALUES (3,ST_GeomFromText('LINESTRING(192783 228138,192612 229814)',3857),'Jalan Lingkar Selatan Kelima'); INSERT INTO roads (id, geom, road_name) VALUES (4,ST_GeomFromText('LINESTRING(189412 252431,189631 259122)',3857),'Jalan Lingkar Barat Kelima'); INSERT INTO roads (id, geom, road_name) VALUES (5,ST_GeomFromText('LINESTRING(190131 224148,190871 228134)',3857),'Jalan Chang'an Timur'); INSERT INTO roads (id, geom, road_name) VALUES (6,ST_GeomFromText('LINESTRING(198231 263418,198213 268322)',3857),'Jalan Chang'an Barat');Kueri informasi objek geometri.
SELECT id, ST_AsText(geom) AS geom, road_name FROM roads; -------------------------------- id | geom | road_name --------+-----------------------------------------+----------- 1 | LINESTRING(191232 243118,191108 243242) | Jalan Lingkar Utara Kelima 2 | LINESTRING(189141 244158,189265 244817) | Jalan Lingkar Timur Kelima 3 | LINESTRING(192783 228138,192612 229814) | Jalan Lingkar Selatan Kelima 4 | LINESTRING(189412 252431,189631 259122) | Jalan Lingkar Barat Kelima 5 | LINESTRING(190131 224148,190871 228134) | Jalan Chang'an Timur 6 | LINESTRING(198231 263418,198213 268322) | Jalan Chang'an Barat (6 baris)Buat indeks.
-- Buat indeks GiST. CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd); VACUUM ANALYZE [table_name] [(column_name)]; -- Contoh: CREATE INDEX sp_geom_index ON ROADS USING GIST(geom); VACUUM ANALYZE ROADS (geom);Ukur dan analisis data spasial.
--Buat Tabel bc_roads: Kolom | Tipe | Deskripsi ------------+-------------------+------------------- gid | integer | ID Unik name | character varying | Nama Jalan the_geom | geometry | Lokasi Geometri (Linestring) --Buat tabel bc_municipality: Kolom | Tipe | Deskripsi -----------+-------------------+------------------- gid | integer | ID Unik code | integer | ID Unik name | character varying | Nama Kota / Kabupaten the_geom | geometry | Lokasi Geometri (Poligon) -- Hitung panjang. SELECT SUM(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads; km_roads ------------------ 70842.1243039643 (1 baris) -- Hitung luas. SELECT ST_Area(the_geom)/10000 AS hectares FROM bc_municipality WHERE name = 'PRINCE GEORGE'; hectares ------------------ 32657.9103824927 (1 baris)Identifikasi hubungan spasial.
--ST_Contains SELECT m.name, sum(ST_Length(r.the_geom))/1000 AS roads_km FROM bc_roads AS r, bc_municipality AS m WHERE ST_Contains(m.the_geom,r.the_geom) GROUP BY m.name ORDER BY roads_km; name | roads_km ----------------------------+------------------ SURREY | 1539.47553551242 VANCOUVER | 1450.33093486576 LANGLEY DISTRICT | 833.793392535662 BURNABY | 773.769091404338 PRINCE GEORGE | 694.37554369147 ... --ST_Covers, lingkaran menutupi lingkaran SELECT ST_Covers(smallc,smallc) AS smallinsmall, ST_Covers(smallc, bigc) AS smallcoversbig, ST_Covers(bigc, ST_ExteriorRing(bigc)) AS bigcoversexterior, ST_Contains(bigc, ST_ExteriorRing(bigc)) AS bigcontainsexterior FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) AS smallc, ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) AS bigc) AS foo; --Hasil smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior --------------+----------------+-------------------+--------------------- t | f | t | f (1 baris) --ST_Disjoint SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry); st_disjoint --------------- t (1 baris) SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry); st_disjoint --------------- f (1 baris) --ST_Overlaps SELECT ST_Overlaps(a,b) AS a_overlap_b, ST_Crosses(a,b) AS a_crosses_b, ST_Intersects(a, b) AS a_intersects_b, ST_Contains(b,a) AS b_contains_a FROM (SELECT ST_GeomFromText('POINT(1 0.5)') AS a, ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') AS b) AS foo a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a ------------+-------------+----------------+-------------- f | f | t | t --ST_Relate SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212'); st_relate ----------- t --ST_Touches SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry); st_touches ------------ f (1 baris) SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry); st_touches ------------ t (1 baris) --ST_Within SELECT ST_Within(smallc,smallc) AS smallinsmall, ST_Within(smallc, bigc) AS smallinbig, ST_Within(bigc,smallc) AS biginsmall, ST_Within(ST_Union(smallc, bigc), bigc) AS unioninbig, ST_Within(bigc, ST_Union(smallc, bigc)) AS biginunion, ST_Equals(bigc, ST_Union(smallc, bigc)) AS bigisunion FROM ( SELECT ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) AS smallc, ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) AS bigc) AS foo; --Hasil smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion --------------+------------+------------+------------+------------+------------ t | t | f | t | t | t (1 baris)Simpan objek geometri.
SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')); st_issimple ------------- t (1 baris) SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)')); st_issimple ------------- f (1 baris) -- Kueri kota dengan area terbesar dan memiliki lebih dari satu cincin. SELECT gid, name, ST_Area(the_geom) AS area FROM bc_municipality WHERE ST_NRings(the_geom) > 1 ORDER BY area DESC LIMIT 1; gid | name | area -----+--------------+------------------ 12 | Anning | 257374619.430216 (1 baris)Hapus ekstensi.
DROP extension ganos_geometry; DROP extension ganos_spatialref;
Referensi SQL
Untuk informasi lebih lanjut, lihat Referensi PostGIS.