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

pg_stat_activity

SELECT * FROM pg_stat_activity

--where usename = 'user'

order by usename;

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

You must copy the latest ST_GEOMETRY and dependent libraries to the PostgreSQL software location

Problem:
Create Enterprise Geodatabase Error

https://geonet.esri.com/thread/56840

SOLUTION:
Place the ST_Geometry libraries in the PostgreSQL lib directory

Geodatabase creation in PostgreSQL relies on the presence of the ST_Geometry library. You must place the appropriate library in the lib directory where PostgreSQL is installed before you can create a geodatabase. ST_Geometry libraries can be found in the DatabaseSupport folder of your ArcGIS client installation.

Since this tutorial assumes your PostgreSQL installation is on a Windows server, you will use the ST_Geometry library found in the Windows64 folder.
Steps:

In Windows Explorer, navigate to the location of the Windows ST_Geometry library in your ArcGIS client installation directory.

The default location for ArcGIS for Desktop on 64-bit Windows operating systems is C:\Program Files (x86)\ArcGIS\Desktop\DatabaseSupport\PostgreSQL\Windows64; on 32-bit Windows operating systems, it is C:\Program Files\ArcGIS\Desktop\DatabaseSupport\PostgreSQL\Windows64.
Copy st_geometry.dll from this location.
Navigate to the PostgreSQL lib directory on the database server and paste the library to that location.

On Windows, the default location is C:\Program Files\PostgreSQL\9.0\lib.

Artık Yeni Şeyler Söyleme Zamanı

Netcad, Netcad TILE, Postgresql, PostGIS, Arcgis Server 10,