空間インデックス - HTN20190109の日記

MySQL

(8.0.29)

https://dev.mysql.com/doc/refman/8.0/ja/creating-spatial-indexes.html
https://dev.mysql.com/doc/refman/8.0/ja/spatial-function-reference.html

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

※空間インデックスのカラムはnot nullが必要

drop procedure proc1;

delimiter //
create procedure proc1(in x bigint)
begin
declare wk1 varchar(100);
declare wk2 varchar(100);

declare i bigint;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
set wk1 = cast( round(rand()*180,2 ) as char) ;
set wk2 = cast( round(rand()*90,2 ) as char) ;
-- point(緯度経度)
set @g = concat('point(',wk2,' ',wk1,')');

insert into tab1(col1,col2,col3) select i,concat('POINT',i), st_geomfromtext(@g,4326) ;
end while;
commit;
end
//
delimiter ;

call proc1(100000);

select count(*) from tab1;
select col1,col2,st_astext(col3) from tab1 limit 10;

analyze table tab1;

CREATE SPATIAL INDEX ind1 ON tab1(col3);

explain analyze
select col1,col2,st_astext(col3)
from tab1
WHERE MBRWithin(col3, st_geomfromtext('LineString(10.00 100.00, 11.00 101.00)', 4326) )
;

explain analyze
select /*+ INDEX(tab1 ind1) */ col1,col2,st_astext(col3)
from tab1
WHERE MBRWithin(col3, st_geomfromtext('LineString(10.00 100.00, 11.00 101.00)', 4326) )
;

Oracle

(19c)

https://docs.oracle.com/cd/F19136_01/spatl/sql-indexing-spatial-data.html#GUID-BB6D7C9E-277B-4F49-BDD4-539CFE88D12C
https://docs.oracle.com/cd/F19136_01/spatl/complex-spatial-queries.html#GUID-66516CA7-D36C-4A73-90FC-28C650DDDF68
https://qiita.com/ryotayamanaka/items/6f9954e63dac03fd44d0

-- 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' -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
);

declare
wk1 number;
wk2 number;
begin
for i in 1..100000 loop
wk1 := round(dbms_random.value()*180,2);
wk2 := round(dbms_random.value()*90,2);

-- point(経度緯度)
insert into tab1(col1,col2,col3) values( i,'POINT'||i, sdo_geometry(2001, 4326, sdo_point_type(wk1, wk2, null), null, null) );

end loop;
end;
/

commit;

select count(*) from tab1;
select * from tab1 fetch first 10 rows only;

exec dbms_stats.gather_table_stats(user, 'TAB1');

※空間索引を作成するには、USER_SDO_GEOM_METADATAにデータ登録が必要。登録していない場合、下記のようなエラーとなる
ORA-29855: ODCIINDEXCREATEルーチンの実行でエラーが発生しました。 ORA-13203:
USER_SDO_GEOM_METADATAビューの読取りに失敗しました。 ORA-13203:
USER_SDO_GEOM_METADATAビューの読取りに失敗しました。 ORA-06512:
"MDSYS.SDO_INDEX_METHOD_10I", 行10

select * from USER_SDO_GEOM_METADATA;

INSERT INTO user_sdo_geom_metadata VALUES (
'tab1',
'col3',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('longitude', -180.0, 180.0, 0.05),
SDO_DIM_ELEMENT('latitude', -90.0, 90.0, 0.05)
),
4326
);
COMMIT;

create index ind1 on tab1(col3)
indextype is mdsys.spatial_index_V2;

explain plan for
select col1,col2,col3,sdo_nn_distance(1) distance_in_km
from tab1
where sdo_nn(col3,
sdo_geometry(2001, 4326, sdo_point_type(15.0, 12.1, null), null, null) ,
'sdo_num_res=5 distance=1000 unit=km', 1) = 'TRUE'
order by distance_in_km;

select * from table(dbms_xplan.display(format => 'ALL') );

PostgreSQL

(14)

https://aginfo.cgk.affrc.go.jp/docs/pgisman/1.3.6/ch04.html#idp66618824
https://qiita.com/takahi/items/37c0a096d2073646ba62

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 geometry(point, 4326)
);

do $$
declare
i bigint;
wk1 varchar(100);
wk2 varchar(100);
wk3 varchar(100);

begin
i := 1;
while i <= 100000 loop
wk1 := round( (random()*180)::numeric ,2)::varchar;
wk2 := round( (random()*90)::numeric ,2)::varchar;
-- point(経度緯度)
wk3 := 'point('||wk1||' '||wk2||')';

insert into tab1 values (i, 'Point'||i, st_geomfromtext(wk3,4326) );
i := i+1;
end loop;
end;
$$;

select count(*) from tab1;
select col1,col2,st_astext(col3) from tab1 limit 10;

analyze tab1;

create index ind1 on tab1 using gist ( col3 );

explain analyze
select col1,col2,st_astext(col3)
from tab1
where st_contains( st_buffer( st_setsrid( st_point( 15.0, 12.1 ) , 4326 ), 1) ,col3)
;

SQL Server

(2019)

https://learn.microsoft.com/ja-jp/sql/relational-databases/spatial/create-modify-and-drop-spatial-indexes?view=sql-server-ver15
https://learn.microsoft.com/ja-jp/sql/t-sql/spatial-geometry/ogc-methods-on-geometry-instances?view=sql-server-ver15

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

begin
set nocount on;
declare @i bigint;
declare @wk1 varchar(100);
declare @wk2 varchar(100);
declare @wk3 varchar(100);

set @i = 1;
begin transaction;
while (@i <= 100000)
begin
set @wk1 = cast( round( rand() * 180,2) as varchar);
set @wk2 = cast( round( rand() * 90,2) as varchar);
-- point(経度緯度)
set @wk3 = 'POINT(' + @wk1 + ' ' + @wk2 + ')';
insert into tab1 values(@i,'POINT'+cast(@i as varchar), geometry::STGeomFromText(@wk3, 4326) );
set @i = @i + 1;
end
end
commit;
go

select count(*) from tab1;
select top(10) col1,col2,col3.STAsText() from tab1;

update statistics tab1;

create spatial index ind1 on tab1(col3)
WITH ( BOUNDING_BOX = ( 0, 0, 180, 90 ) );

select col1,col2,col3.STAsText()
from tab1
where col3.STWithin( geometry::STGeomFromText('LineString(80.00 50.00, 150.00 70.00)', 4326) ) = 1
;