地理空間情報

DB

(8.0.26)
https://note.com/moli9ma/n/n3b530e3ad1d5
https://qiita.com/onunu/items/59ef2c050b35773ced0d


追加インストールは不要


drop table tab1;
create table tab1 (
 col1 int primary key,
 col2 varchar(255),
 col3 geometry SRID 4326
);


-- point(緯度 経度)★
insert into tab1 values (1, 'PointA', st_geomfromtext('point(12.1 15.0)',4326));
insert into tab1 values (2, 'PointB', st_geomfromtext('point(10.4 -61.3)',4326));


※ SRID -> データベースで空間参照系 (SRS)を識別する
4326 -> WGS 84経度緯度

select * from tab1;

-- PointAとPointBの距離
select st_distance(t1.col3, t2.col3)/1000 km
from tab1 t1
inner join tab1 t2
on 1=1
where t1.col2 = 'PointA'
and t2.col2 = 'PointB'
;


+-------------------+
| km                |
+-------------------+
| 8305.060442111293 |
+-------------------+

(19c)
Oracle Spatial

https://support.oracle.com/knowledge/Oracle%20Database%20Products/270588_1.html
https://www.infoscoop.org/blogjp/2015/01/21/tutorial_for_geodb/

-- 1 Oracle Spatialインストール
-- インストール対象のPDBはオープンが必要

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/oracle -v  -b mdinst -c  'PDB$SEED CDB$ROOT PDB1 PDB2'   -U "SYS"/"oracle" $ORACLE_HOME/md/admin/mdinst.sql

 

-- インストール後確認

connect / as sysdba
set serveroutput on

execute validate_sdo;

select comp_id, control, schema, version, status, comp_name from dba_registry 
where comp_id='SDO';

select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;

 


-- 2 動作確認

conn test/test@pdb1

drop table tab1 purge;
create table tab1 (
    col1 int primary key,
    col2 varchar2(255),
    col3 sdo_geometry
);

-- point(経度 緯度)
insert into tab1 values (1, 'POINTA', sdo_geometry(2001, 4326, sdo_point_type(15.0, 12.1, null), null, null));
insert into tab1 values (2, 'POINTB', sdo_geometry(2001, 4326, sdo_point_type(-61.3, 10.4, null), null, null));

commit;
select * from tab1;

-- PointAとPointBの距離
select sdo_geom.sdo_distance(t1.col3, t2.col3)/1000 km
from tab1 t1
inner join tab1 t2
on 1=1
where t1.col2 = 'POINTA'
and t2.col2 = 'POINTB'
;

        KM
----------
8305.05876

(14)
PostGIS

https://postgis.net/docs/manual-3.0/postgis-ja.html#install_requirements
https://out-engineer.blogspot.com/2011/02/postgiscentos.html
http://tialalight.cocolog-nifty.com/diary/2010/10/post-14b7.html
https://qiita.com/uhey22e/items/69285a14b4476dc73664
https://note.com/kotarai/n/nd2bfbafbef6e#y3BtO

OS: CentOS 8

-- 1 PostGISインストール
-- 1.1 必要パッケージのインストール

su - postgres

sudo dnf install libxml2-devel sqlite-devel libtiff-devel libcurl-devel


-- 1.2 geosのインストール
cd
wget http://download.osgeo.org/geos/geos-3.9.2.tar.bz2
tar xvjf geos-3.9.2.tar.bz2
cd geos-3.9.2
./configure
make
sudo make install


-- 1.3 projのインストール

cd
wget https://download.osgeo.org/proj/proj-8.2.0.tar.gz
tar xvzf proj-8.2.0.tar.gz
cd proj-8.2.0
./configure
make
sudo make install


-- 1.4 PostGISのインストール

cd
wget http://download.osgeo.org/postgis/source/postgis-3.1.4.tar.gz
tar xvfz postgis-3.1.4.tar.gz
cd postgis-3.1.4

PG_CONFIG=/usr/pgsql-14/bin/pg_config ./configure --without-protobuf --without-raster
make PG_CONFIG=/usr/pgsql-14/bin/pg_config 
sudo make PG_CONFIG=/usr/pgsql-14/bin/pg_config install


-- 1.5 ライブラリパス設定
sudo vi /etc/ld.so.conf.d/geos.conf
/usr/local/lib

sudo /sbin/ldconfig


-- 1.6 拡張インストール
psql test

SELECT name, default_version,installed_version
FROM pg_available_extensions 
WHERE name LIKE 'postgis%'
;

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

\dx

 

-- 2 動作確認

drop table tab1;
create table tab1 (
 col1 int primary key,
 col2 varchar(255),
 col3 geography(point, 4326)
);


-- point(経度 緯度)
insert into tab1 values (1, 'PointA', st_geomfromtext('point(15.0 12.1)',4326));
insert into tab1 values (2, 'PointB', st_geomfromtext('point(-61.3 10.4)',4326));

select * from tab1;

-- PointAとPointBの距離
select st_distance(t1.col3, t2.col3)/1000 km
from tab1 t1
inner join tab1 t2
on 1=1
where t1.col2 = 'PointA'
and t2.col2 = 'PointB'
;

        km
------------------
 8305.05875728407

(2019)
https://mrgchr.hatenablog.com/entry/2019/09/21/120000
https://docs.microsoft.com/ja-jp/sql/t-sql/spatial-geography/stdistance-geography-data-type?view=sql-server-ver15


追加インストールは不要


drop table tab1;
create table tab1 (
 col1 int primary key,
 col2 varchar(255),
 col3 geography
);


-- point(経度 緯度)
insert into tab1 values (1, 'PointA',geography::STGeomFromText('POINT(15.0 12.1)', 4326) ) ;
insert into tab1 values (2, 'PointB',geography::STGeomFromText('POINT(-61.3 10.4)', 4326) ) ;


select * from tab1;

-- PointAとPointBの距離
select t1.col3.STDistance(t2.col3)/1000 km
from tab1 t1
inner join tab1 t2
on 1=1
where t1.col2 = 'PointA'
and t2.col2 = 'PointB'
;

8305.06040016007