Kent Teknik

postgis geomerty columns

select
*
from geometry_columns
where
f_table_name like 'geoplan%'
;

Postgresql otomatik yedek alma

fonksiyon oluşturulur

CREATE OR REPLACE FUNCTION admin.copy_haritapaftabin(IN p_path text, IN p_filename_prefix text, OUT file_and_path text)
RETURNS text AS
$BODY$
DECLARE
qry TEXT;
BEGIN
file_and_path := RTRIM(p_path,'/') || '/' || p_filename_prefix || '_' || to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD-HH-MI'::text) || '.csv';

qry := FORMAT('COPY (select * from harita.paftabin) TO %L CSV HEADER',file_and_path);
EXECUTE qry;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER
COST 100;

select komutu tabloyu csv olarak dışarı alır. job yapılabilir

SELECT admin.copy_haritapaftabin('e:\YEDEK\VT\csv','haritapaftabin');

Network Lisans İzleyicisi Program Numaraları

http://portal.netcad.com.tr/display/HELP/Network+Lisans+Izleyicisi?src=search

geoserver labeling not supported with qgis

QGIS does not export labels use UDIG to do styling and labeling (generate SLD) or ArcGIS ArcMap with Arc2Earth.

https://gis.stackexchange.com/questions/149033/exporting-qgis-styles-to-sld-for-geoserver

set SRID postgis

ALTER TABLE mytable ALTER COLUMN geom TYPE geometry(MultiPolygon,4326) USING ST_SetSRID(geom,4326);

set SRID postgis

ALTER TABLE uygulama.uygulama_multi ALTER COLUMN poly type geometry(Polygon, 5256);

pg_stat_activity

SELECT * FROM pg_stat_activity

--where usename = 'user'

order by usename;

Netcad de wms cach temizleme batch dosyası

del /s /q %userprofile%\AppData\Local\Temp\WMSCache ever the file is*

rmdir /s /q %userprofile%\AppData\Local\Temp\WMSCache ever the file is\

oruxmaps yükleme

http://www.kursatozcan.com/oruxmaps_uydu_haritasi_download

netcad 7.7 de online google haritası ekleme

Netcad Portal Konu Linki

http://portal.netcad.com.tr/display/HELP/NETCAD+Forum/?sayfa=konu&konuId=96480

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

Referanslar\Ekle\Online Haritalar\Ekle -->Online Harita sekmesi

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

Sokak Haritası için

URL: mt{$s}.google.com/vt/lyrs=m&hl=tr-TR&gl=cn&x={$x}&y={$y}&z={$z}&s=

Alt Alanlar: 0,1,2,3

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

Uydu Görüntüsü İçin

URL: mt{$s}.google.com/vt/lyrs=s&gl=tr&x={$x}&y={$y}&z={$z}&s=

Alt Alanlar: 0,1,2,3

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

Karma Harita için

URL: mt{$s}.google.com/vt/lyrs=h&hl=tr-TR&gl=tr&x={$x}&y={$y}&z={$z}&s=

Alt Alanlar: 0,1,2,3

primarykey i "idd" olan tabloda çift "objectid" li verilerin temizlenmesi

ALTER TABLE uygulama.uygulama_single

ADD COLUMN cift character varying(25);

select idd from uygulama.uygulama_single

where idd not in

(

select max(idd)

from uygulama.uygulama_single

group by objectid)

;

update uygulama.uygulama_single

set cift = 1

where idd not in

(

select max(idd)

from uygulama.uygulama_single

group by objectid)

;

delete from uygulama.uygulama_single

where cift = '1'

;

postgis - multipolygon tipindeki verinin düzenlenerek single forma dönüştürülmesi - tüm script

create table uygulama.uygulama_single as
SELECT objectid, ilcekod, mahallekod, yapankod, kontroledenkod, uygulamatipikod,
uygulamadurumkod, askicikistarih, askiinistarih, encumentarih,
encumensayi, alan, ifraztevhidalan,

(st_dump(poly)).geom :: geometry (polygon, 5256) as poly,

aciklama, dosya, yil,
deger1, deger2
FROM uygulama.uygulama
;

create table uygulama.uygulama_multi as
select * from uygulama.uygulama
;

ALTER TABLE uygulama.uygulama_multi ADD COLUMN idd integer;

CREATE SEQUENCE uygulama.sq_tum
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

update uygulama.uygulama_multi set idd = nextval('uygulama.sq_tum')
;

ALTER TABLE uygulama.uygulama_multi ALTER COLUMN idd SET NOT NULL;
ALTER TABLE uygulama.uygulama_multi ALTER COLUMN idd SET DEFAULT nextval('uygulama.sq_tum'::regclass);

ALTER TABLE uygulama.uygulama_multi ADD CONSTRAINT pk_uygulama_multi PRIMARY KEY(idd);

delete from uygulama.uygulama_multi
;

INSERT INTO uygulama.uygulama_multi(
objectid, ilcekod, mahallekod, yapankod, kontroledenkod, uygulamatipikod,
uygulamadurumkod, askicikistarih, askiinistarih, encumentarih,
encumensayi, alan, ifraztevhidalan, poly, aciklama, dosya, yil,
deger1, deger2, idd)

SELECT objectid, ilcekod, mahallekod, yapankod, kontroledenkod, uygulamatipikod,
uygulamadurumkod, askicikistarih, askiinistarih, encumentarih,
encumensayi, alan, ifraztevhidalan,

poly,

aciklama, dosya, yil,
deger1, deger2, nextval('uygulama.sq_tum')
FROM uygulama.uygulama
;

delete from uygulama.uygulama_single
;

INSERT INTO uygulama.uygulama_single(
objectid, ilcekod, mahallekod, yapankod, kontroledenkod, uygulamatipikod,
uygulamadurumkod, askicikistarih, askiinistarih, encumentarih,
encumensayi, alan, ifraztevhidalan, poly, aciklama, dosya, yil,
deger1, deger2, idd)

SELECT objectid, ilcekod, mahallekod, yapankod, kontroledenkod, uygulamatipikod,
uygulamadurumkod, askicikistarih, askiinistarih, encumentarih,
encumensayi, alan, ifraztevhidalan,

(st_dump(poly)).geom :: geometry (polygon, 5256),

aciklama, dosya, yil,
deger1, deger2, nextval('uygulama.sq_tum')
FROM uygulama.uygulama
;

create or replace view uygulama.single_to_multi as
select distinct objectid,

(st_dump (ST_Multi(st_union(poly)) )).geom :: geometry (polygon,5256) as poly
--ST_Multi(st_union(poly)) :: geometry (MultiPolygon,5256) as poly

from uygulama.uygulama_single
group by objectid
;

update uygulama.uygulama_multi
set poly = null;

ALTER TABLE uygulama.uygulama_multi ALTER COLUMN poly type geometry(Polygon, 5256);

update uygulama.uygulama_multi um
set poly = sm.poly
from uygulama.single_to_multi sm
where um.objectid = sm.objectid
;

postgis - multipolygon tipindeki geometry kolonunu polygon tipine çevirme

ALTER TABLE uygulama.uygulama_multi ALTER COLUMN poly type geometry(Polygon, 5256);

komutu işe yarayacaktır. ancak geometry kolonunun içi boş olmalı

-- 1. tablo verisi baska bir tabloya single geometry tipiyle yedeklenir

create table uygulama.uygulama_single_test as

SELECT objectid, ilcekod, mahallekod, yapankod, kontroledenkod, uygulamatipikod,

uygulamadurumkod, askicikistarih, askiinistarih, encumentarih,

encumensayi, alan, ifraztevhidalan,

(st_dump(poly)).geom :: geometry (polygon, 5256) as poly,

aciklama, dosya, yil,

deger1, deger2, idd

FROM uygulama.uygulama_multi

;

--2. orjinal tablonun geometry kolonu boşaltılır

update uygulama.uygulama_multi

set poly = null;

--3. geometry kolonu tipi dönüştürülür.

ALTER TABLE uygulama.uygulama_multi ALTER COLUMN poly type geometry(Polygon, 5256);

--4. single geometry halindeki geometri bilgisi yedek tablodan orjinal tabloya update edilir.

update uygulama.uygulama_multi um

set poly = sm.poly

from uygulama.single_to_multi sm

where um.objectid = sm.objectid

;

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

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

Labels - Etiketler