Kent Teknik

postgis geomerty columns

2018-11-22T15:11:00.000+03:00

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

Postgresql otomatik yedek alma

2018-11-12T11:44:00.001+03:00

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

Netgis Server Yardım

2018-11-09T11:07:00.002+03:00

http://portal.netcad.com.tr/pages/viewpage.action?pageId=105316675

netcadde wms oluşturmak için workspace export

2018-11-09T08:13:00.001+03:00

http://portal.netcad.com.tr/pages/viewpage.action?pageId=101778409

Online Wms Viewer

2018-11-08T14:03:00.000+03:00

https://wms-viewer-online.appspot.com/indexmap.html

geoserver labeling

2018-11-08T13:59:00.000+03:00

https://docs.geoserver.org/stable/en/user/styling/sld/reference/labeling.html

Network Lisans İzleyicisi Program Numaraları

2018-11-08T09:38:00.000+03:00

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

geoserver labeling not supported with qgis

2018-11-08T08:27:00.001+03:00

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

2018-11-06T09:09:00.001+03:00

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

set SRID postgis

2018-11-06T09:07:00.000+03:00

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

pg_stat_activity

2018-11-06T08:42:00.001+03:00

SELECT * FROM pg_stat_activity

--where usename = 'user'

order by usename;

Netcad de wms cach temizleme batch dosyası

2018-11-05T12:57:00.001+03:00

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

2018-11-03T18:17:00.001+03:00

http://www.kursatozcan.com/oruxmaps_uydu_haritasi_download

netcad 7.7 de online google haritası ekleme

2018-11-02T13:52:00.001+03:00

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

2018-11-01T11:46:00.001+03:00

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

2018-11-01T11:41:00.001+03:00

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

2018-11-01T11:39:00.001+03:00

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

2017-06-13T15:50:00.000+03:00

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/

2017-06-06T10:27:00.000+03:00

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ı

2017-04-24T15:44:00.000+03:00

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

GROUP BY objectid

ORDER BY sum

Solar atlas

2017-04-01T09:08:00.001+03:00

http://globalsolaratlas.info/

substr, replace, to_date, BMK

2017-03-29T17:23:00.000+03:00

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ı

2017-03-25T17:48:00.000+03:00

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

2017-03-25T15:45:00.000+03:00

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

2017-03-20T23:14:00.000+03:00

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