check geometry

ST_IsValidReason — Returns text stating if a geometry is valid or not and if not valid, a reason why.

https://postgis.net/docs/reference.html#Geometry_Editors

sqlfiddle.com/

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.http://www.sqlfiddle.com/

SQL Fiddle | A tool for easy online testing and sharing of database problem... Application for testing and sharing SQL queries.

kapalı alanın vertex sayısı

SELECT distinct objectid, sum(ST_NPoints(poly)) FROM public.geoplan_fonksiyon_5000

GROUP BY objectid

ORDER BY sum

Solar atlas

http://globalsolaratlas.info/

substr, replace, to_date, BMK

select

s.bilgi, s.detay1

, replace("bilgi", 'BMK=', '') as bmk
, substr(replace("bilgi", 'BMK=', ''),7,4) as yil
, substr(replace("bilgi", 'BMK=', ''),4,2) as ay
, substr(replace("bilgi", 'BMK=', ''),1,2) as gun
, substr(replace("bilgi", 'BMK=', ''),12,3) as sayi
, bsmtarih
, bsmsayi


from

planbesbin.mnip_sinir s

where

s.bilgi like 'BMK=%'


--------------------

UPDATE planbesbin.mnip_sinir s
SET bsmtarih =

to_date(

substr(replace("bilgi", 'BMK=', ''),7,4) ||'-'||
substr(replace("bilgi", 'BMK=', ''),4,2) ||'-'||
substr(replace("bilgi", 'BMK=', ''),1,2)
, 'YYYY.MM.DD'
)
,
bsmsayi = cast(substr(replace("bilgi", 'BMK=', ''),12,3) as integer)

where
s.bilgi like 'BMK=%'

to_char - tarih formatı

CREATE OR REPLACE FUNCTION planbesbin.fn_bmk()
RETURNS trigger AS
$$
BEGIN

SELECT ('BMK=' || TO_CHAR(s.bsmtarih, 'DD.MM.YYYY') || '/' || s.bsmsayi )
INTO NEW.bmk

FROM
planbesbin.mnip_sinir s
,
planbesbin.mnip_cizgi c



WHERE
st_intersects (ST_StartPoint(NEW.poly), s.poly)
AND ST_Disjoint (ST_EndPoint(NEW.poly), s.poly)

AND NEW.mnip_kod = 80101 AND NEW.detay1= '1'

;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

----------------------


CREATE TRIGGER tg_bmk_BEFORE
BEFORE INSERT OR UPDATE ON planbesbin.mnip_cizgi
FOR EACH ROW EXECUTE PROCEDURE planbesbin.fn_bmk();


CREATE TRIGGER tg_bmk_AFTER
AFTER INSERT OR UPDATE ON planbesbin.mnip_cizgi
FOR EACH ROW EXECUTE PROCEDURE planbesbin.fn_bmk();

TRIGGER - bmk

CREATE OR REPLACE FUNCTION planbesbin.fn_bmk()
RETURNS trigger AS
$$
BEGIN

SELECT ('BMK=' || s.bsmtarih ||'/'|| s.bsmsayi)
INTO NEW.bmk

FROM
planbesbin.mnip_sinir s
,
planbesbin.mnip_cizgi c



WHERE
st_intersects (ST_StartPoint(NEW.poly), s.poly)
AND ST_Disjoint (ST_EndPoint(NEW.poly), s.poly)

AND NEW.mnip_kod = 80101 AND NEW.detay1= '1'

;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

----------------------


CREATE TRIGGER tg_bmk_BEFORE
BEFORE INSERT OR UPDATE ON planbesbin.mnip_cizgi
FOR EACH ROW EXECUTE PROCEDURE planbesbin.fn_bmk();


CREATE TRIGGER tg_bmk_AFTER
AFTER INSERT OR UPDATE ON planbesbin.mnip_cizgi
FOR EACH ROW EXECUTE PROCEDURE planbesbin.fn_bmk();

Piton scripting

http://darrylmcleod.com/wp-content/uploads/2016/06/Python-Scripting-for-ArcGIS.pdf

Oracle giriş

http://yukselveysel.blogspot.com.tr/p/oracle-11g-r2-database-olusturmaknetca.html

buffer, offset, reverse, yol kalınlık

create table planbesbin.mnip_yol_kalinlik as
select
objectid, fonksiyon, fonksiyon_tip, mnip_kod, abs(cizgikalinligi) as cizgikalinligi,
st_buffer
(
(
ST_OffsetCurve
(ST_Reverse(poly),
abs(cizgikalinligi)/2) --offset miktarı abs(cizgikalinligi)/2)
),
(abs(cizgikalinligi)/2), -- buffer miktarı
'endcap=flat join=round'
),
ST_Length (poly) as uzunluk
FROM planbesbin.mnip_cizgi
where
cizgikalinligi is not null
and ST_Length (poly) > 0
and pasif is null
;

st_buffer, abs(absolute value),

-- DROP TABLE planbesbin.mnip_cizgi_alan;
create table planbesbin.mnip_cizgi_alan
as
SELECT objectid, fonksiyon, fonksiyon_tip, st_buffer(poly, abs(cizgikalinligi), 'endcap=flat join=round') as poly, mnip_kod, cizgikalinligi,
ada_cizgikalinligi, enerjinakil_gerilim, mania_yukseklik, tunel_genislik,
yayayolu_genislik, bmk, pasif, detay1, islem, detay2
FROM planbesbin.mnip_cizgi
where cizgikalinligi is not null
;

alan tipini değiştir

ALTER TABLE planlama.uip ALTER COLUMN objectid TYPE integer USING (objectid::integer);
ALTER TABLE public.uygulama ALTER COLUMN ncgeomvali TYPE integer USING (ncgeomvali::integer);
ALTER TABLE public.uygulama ALTER COLUMN alan TYPE integer USING (alan::integer);

createCenterlines

http://www.ian-ko.com/ET_GeoWizards/UserGuide/createCenterlines.htm

spatial trigger

kaynak:
http://gis.stackexchange.com/questions/49252/postgis-trigger-doesnt-work

---------------------------------------------
CREATE OR REPLACE FUNCTION fn_ilceadi()
RETURNS trigger AS
$$
BEGIN

SELECT i."ADI"
INTO NEW.ilce
FROM admin."KBBSILCE" i
WHERE ST_Intersects(st_centroid(NEW.poly), i.poly);

RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';


---------------------------------------------

CREATE TRIGGER tg_ilceadi
BEFORE INSERT OR UPDATE ON planbesbin.mnip_alan
FOR EACH ROW EXECUTE PROCEDURE fn_ilceadi();

spatial trigger

CREATE OR REPLACE FUNCTION fn_ilceadi()
RETURNS trigger AS
$$
BEGIN

SELECT i."ADI"
INTO NEW.ilce
FROM admin."KBBSILCE" i
WHERE ST_Intersects(st_centroid(NEW.poly), i.poly);

RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

----------------------


CREATE TRIGGER tg_ilceadi
BEFORE INSERT OR UPDATE ON planbesbin.mnip_alan
FOR EACH ROW EXECUTE PROCEDURE fn_ilceadi();

ST_SymDifference

http://postgis.net/docs/ST_SymDifference.html

st_difference

http://postgis.net/docs/ST_Difference.html

ST_Force_2D(poly)

select ST_Force_2D(poly) from admin.bmk_union_point

st_makeline

create table admin.line as
SELECT
grup_id,
ST_MakeLine(poly) AS poly
FROM admin.bmk_point
where grup_id is not null
GROUP BY grup_id
;

alan hesapla, st_area

select a.alan, round(st_area((poly::geometry))::numeric,3) from planbesbin.mnip_alan a


update planbesbin.mnip_alan a
set alan = (st_area((poly)))

hem geometrik hem de ortak kolonlu 3 tablo sorgu

SELECT
distinct (i."ADI", p.mnip_kod, k."TABLENAME", k."DOMAINNAME", k."VALUE"), count (*), sum(st_area(p.poly))
FROM
planbesbin.mnip_alan p, admin."KBBSILCE" i, planbesbin.mnip_kod k
WHERE
st_intersects(st_centroid(p.poly), i.poly)
and k.mnip_kod = p.mnip_kod
group by (i."ADI", p.mnip_kod, k."TABLENAME", k."DOMAINNAME", k."VALUE")
order by (i."ADI", p.mnip_kod, k."TABLENAME", k."DOMAINNAME", k."VALUE")

centroidi içine düşen poligonun bilgisini aldırma, st_centroid, geom

update planbesbin.mnip_alan p
set mahalle = m."ADI"
from admin."KBBSMAHALLE" m
where st_intersects(st_centroid(p.poly), m.poly)

netcad indirme

http://portal.netcad.com.tr/display/HELP/Indirilebilir+Dosyalar?from=JoFuBg

st_intersects, count, sum_area

SELECT
distinct (i."ADI", p.mnip_kod, k."TABLENAME", k."DOMAINNAME", k."VALUE"), count (*), sum(st_area(p.poly))
FROM
planbesbin.mnip_alan p, admin."KBBSILCE" i, planbesbin.mnip_kod k
WHERE
st_intersects(p.poly, i.poly)
and k.mnip_kod = p.mnip_kod
group by (i."ADI", p.mnip_kod, k."TABLENAME", k."DOMAINNAME", k."VALUE")
order by (i."ADI", p.mnip_kod, k."TABLENAME", k."DOMAINNAME", k."VALUE")