PostGIS Special Functions Index
PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.
Postgres Installation and initiation:
Check below setting in: Terminal-> Preferences
shells open with : /bin/bash
1) nano .bash_profile
Give the path as :-
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin
File: .bash_profile:
export PATH=PATH:/Applications/Postgres.app/Contents/Versions/latest/bin
3. ctrl+o → Enter
4. ctrl+x
Keep Postgres server on.
Also need to be add postgis extension as below.
Example of Some Function as below.
->Run below command in terminal.
Shp2pgsql -I -D -s 4326 '/Users/rachin_jain/Documents/Detection_Data/BR27009/Unmatched_mail_data/Address_issue_todo.shp'
br27009.unmatched_wave_todo | psql -d postgres
b) Copy Data from csv to postgress in local system :-
copy poi_br23009.poi from '/Users/rachin_jain/Documents/mail_creation/POI_DATA/23009/123/poi_23009_final_upload.csv' HEADER DELIMITER ',' CSV;
c)Copy Data from csv to postgress in server :-
psql -h 17.76.43.67 -p 5433 -U rachin_jain -d prc_addressing_db -c "\copy poi_vendor_data_latest.br12001_poi_final from '/Users/rachin_jain/Documents/mail_creation/POI_DATA/12001/BR12001.csv' HEADER DELIMITER ',' CSV;"
d) Loading Shp into Postgress(Server):-
shp2pgsql -I -D -s 4326 '/Users/rachin_jain/Downloads/BR9002/files/BR90022018-11-06a/BR9002_06112018_0_UNMATCHED_PRIMARY.shp' br17031.unmatched_primary | psql -h 19.76.23.67 -p 5433 -d prc_addressing_db -U rachin_jain
e) Download shp from Postgress(Server) :-
pgsql2shp -f /Users/rachin_jain/Documents/Analysis_for_coverage/MAIL_data/test.shp -h 19.76.53.67 -P password123 -p 5433 -d prc_addressing_db br24005.br24005_poi
f) Download shp from Postgress(local) :-
pgsql2shp -f /Users/rachin_jain/Documents/Analysis_for_coverage/MAIL_data/test.shp -h localhost -P postgres -p 5432 -d postgres br3001.br3001_cl_parcel
g)example to load geopackage in postgress
Following is a command-line example to load geopackage (.gpkg) data to PostGIS database.
ogr2ogr -f PostgreSQL "PG:user=rachinj dbname=Geocoding schemas=uk" /Users/rachin_jain/Desktop/UK/Difference/Birimingham/Differ_data_xyz.gpkg
2) Add Geometry column & update geom:-
SELECT AddGeometryColumn ('br27009','unmatched_wave_todo','geom',4326,'POINT',2);
update br27009.unmatched_wave_todo
set geom = ST_SetSRID(ST_MakePoint(lon::float, lat::float), 4326);
Add multiple column having default value:-
Example:-
alter table b5.buildings
add column house_num varchar(255),
add column hno_is_dup bool default(false),
add column is_dup bool default(false),
add column is_in_repo bool default(false);
3) Index Query for Geometry column:-
CREATE INDEX detection_geom_sidx
ON br27009.unmatched_wave_todo USING gist (geom);
Drop INDEX br27009.MATCH_INGEST_hsn_idx1;
CREATE INDEX MATCH_INGEST_hsn_idx1 ON br27009.unmatched_wave_todo(hsn);
4) Cast Command if geomatry not updating:-
CAST(nullif(long, '') AS float )
CAST(nullif(lat, '') AS float )
Example:-
update br23008_mail_build.interpolate_23008 set long = CAST(nullif(long, '') AS float )
update br23008_mail_build.interpolate_23008 set lat = CAST(nullif(lat, '') AS float )
update br23008_mail_build.interpolate_23008
set geom = ST_SetSRID(ST_MakePoint(long::float, lat::float), 4326);
buffer Query 10 mtr----
alter table br27009.unmatched_wave_todo
Add column pt_buff10m geometry
Update br27009.unmatched_wave_todo
Set pt_buff10m = st_buffer(geom, 0.0001)
for 0.5 mtr buffer example
alter table br27009.unmatched_wave_todo
Add column pt_buff_0_5m geometry
Update br27009.unmatched_wave_todo
Set pt_buff_0_5m = st_buffer(geom, 0.000005)
For 20 mtr:-st_buffer(geom, 0.0002)
For 50 mtr:-st_buffer(geom, 0.0005)
For 100 mtr:-st_buffer(geom, 0.0010)
5) Intersect query spatially :-
st_intersects(a.geom,b.geom)
create table br27009.Detection_Todos_with20m_CL_parcel_with_matching_HSN
as
select a.ticket_id,a.todo_id,st_astext(a.geom),a.todo_descr,a.todo_title,a.house_number,b.addr,b.parcel_id,b.zip,b.house_num_cl
from
br27009.detection_todo_after_remove_matching_with_cl_parcel a,
br27009.CL_parcel_after_exclude_match_ingest_parcel b
where st_intersects(a.pt_buff20m,b.geom) and a.house_number=b.house_num_cl
6) For Checkinh Stack point of same table:-
alter table br27009.detection_todo_after_remove_null_featureid_47596
Add column pt_buff_0_5m geometry
Update br27009.detection_todo_after_remove_null_featureid_47596
Set pt_buff_0_5m = st_buffer(geom, 0.000005)
create table br27009.stack_detection_todo as
select a.* from br27009.detection_todo_after_remove_null_featureid_47596 a,
br27009.detection_todo_after_remove_null_featureid_47596 b
where st_intersects(a.pt_buff_0_5m,b.geom) and a.house_number!=b.house_number
7) Cluster Query:-
ST_ClusterWithin(geom,0.0005)
create table br16006.br16006_building_buff as
SELECT generate_subscripts((ST_ClusterWithin(geom,0.0005)),1) gid,
ST_Buffer(unnest(ST_ClusterWithin(geom,0.0005)),0.0002) geom,
ST_NumGeometries( unnest(ST_ClusterWithin(geom,0.0005))) bldg_count
FROM br16006.br16006_building;
8) Case Function:-
case when prefix is null then 'np' else 'p' end
Example:-1
update br27009.unmatched_wave_todo_136954
set is_detection_flag = CASE WHEN (todo_descr like '% detection%') THEN 'Y' ELSE 'N' END;
Example:-2
update br3003_analysis.cl_parcel_queens set is_flag =case when
(parcel_id
in(select parcel_id from br3003_analysis.address_point_in_cl_parcel)) then 'Yes' else 'No' end
Example:-3
update br3004_analysis.temp
set low_count = (case when np_count>p_count then 'p_count' else 'np_count' end);
Example:-4
update br3004_analysis.temp a
set p_count = b.p_count
from
( select address, (case when prefix is null then 'np' else 'p' end) as pref, count(*) as p_count
from br3004_analysis.roadsegment_having_prefix_connector_mixed_apt
where prefix is not null
group by address, pref) b
where a.address = b.address;
9) Remove duplicate Value based on column:-
delete from br3001.todo_falling_in_range_3node_base where ctid in (
SELECT UNNEST(ARRAY_REMOVE(all_ctids,actid))
FROM (
SELECT
MIN(b.ctid) AS actid,
ARRAY_AGG(ctid) AS all_ctids
FROM br3001.todo_falling_in_range_3node_base b
GROUP BY todo_id
HAVING COUNT(*) > 1) c);
10) Calculate Start Point and end point geomatry of line :-
ST_StartPoint(ST_LineMerge(geom))as geom
ST_EndPoint(ST_LineMerge(geom)) as geom
Example:-
select from_node as node, ST_StartPoint(ST_LineMerge(geom))as geom
from br3001."road_segment_BR_3001"
union all
select to_node as node, ST_EndPoint(ST_LineMerge(geom)) as geom
from br3001."road_segment_BR_3001"
11) Even Odd Function:-
Even= CAST(nullif(house_number, '') AS integer) %2 = 0;
Odd= CAST(nullif(house_number, '') AS integer) %2 != 0;
12) Like ANY Array Function:-
LIKE ANY(ARRAY['%1%','%2%','%3%','%4%','%5%','%6%','%7%','%8%','%9%','%0%'])
LIKE ANY(ARRAY['% NW%','% NE%','% SW%','% SE%','% N','% E','% W','% S'])
NOT LIKE ALL (ARRAY['NW','NE','SW','SE','N','E','W','S'])
Example:-
select btrim(regexp_replace("street",'(.*)( .*?)','\2')),street from br23009_feb22_deliverables6.br23009_match_ingest1
where btrim(regexp_replace("street",'(.*)( .*?)','\2')) not in (select suffix_form FROM spec.abbreviations)
and btrim(regexp_replace("street",'(.*)( .*?)','\2')) NOT LIKE ALL (ARRAY['NW','NE','SW','SE','N','E','W','S'])
13) Buffer and dissolve Query:-
ST_Union(ST_Buffer(geom, 0.0015))
Example:-
drop table if exists b5.buildings_dup_buffer_dissolve;
create table b5.buildings_dup_buffer_dissolve as
select house_num, ST_Union(ST_Buffer(geom, 0.0015)) as geom
from b5.buildings where is_dup = true
---Create duplicates buffer and dissolve ---- Single part
drop table if exists b5.buildings_dup_buffer_single_part;
create table b5.buildings_dup_buffer_single_part as
select house_num, (ST_Dump(geom)).geom as geom
from b5.buildings_dup_buffer_dissolve;
14) Distance Query:-
Function:- ST_Distance(a.geom::geography, b.geom::geography)
Function:- ST_DWithin(a.geom, b.geom, 0.0015)
Example1:- (200 Mtr)
select a.*,b.* from analysis_br23003.unmatched_primary a , analysis_br23003.mail_interpolate b where
a.hsn_street = b.hsn_street and
ST_DWithin(a.geom, b.geom, 0.0020)
Example2:-
update nmdr.match_ingest_poi_addr a
set feature_id_tm = b.feature_id,
address_tm = b.address,
distance = round(st_distance(a.geom::geography, b.geom::geography)::numeric, 2)
from nmdr.address_points b where
upper(a.cl_address_ordinal_mod) = upper(b.address);
Points within parcels:-
Function:- ST_Within(a.geom, b.geom)
Example:-
create table br4004.mail_points_within_cl_parcels as
select a.*, b.geom_repo as mail_geom
from br4004.cl_parcels a, br4004.mail2 b
where ST_Within(a.geom, b.geom)
15) Copy table data into CSV file:-
copy (select row_number() over(order by cluster_id, s_no, street, house_num) as sl_no, feature_id, house_num, street, location, is_duplicate
from b5.buildings_not_in_repo2
order by cluster_id, s_no, street, house_num)
to '/Users/rachin_jain/Documents/b5/Validation/buildings_not_in_repo.csv' with csv header delimiter ',';
Copy table data from server into csv file:-
psql -h 17.76.43.67 -p 5433 -U rachin_jain -d prc_addressing_db -c "\copy (select id,gid,longitude,latitude,poi_vendor_id,poi_name,address,city_name,state_code,postal_code,country,poi_vendor_name,business_category,modified_address,fips,postal_code_final,standardized_address
from poi_vendor_data_latest.br24003_poi) to '/Users/rachin_jain/Documents/mail_creation/POI_DATA/BR24003_POI.csv'with csv header delimiter ','"
16) Substring Function :-
1)if address like "009 AB Road" then output is "9"
house_number=TRIM(LEADING '0' FROM substring(column_Name from '^[0-9]+'))
2)
A) if address like "009 AB Road" then output is "009"
house_number=substring(column_Name from '^[0-9]+')
house_number=substring(feature_id from '^\d+$')
B) if address like "119A E 4TH ST" then output is "119A"
house_number=substring(todo_title,'^[0-9A-Z]+')
3) If Value like F-100234444000 THEN output is "100234444000"
a) right(feature_id ,length(feature_id)-2)
b) substring(feature_id from '[0-9]+')
c) substring(feature_id from '\d+$')
Example:- select substring(todo_id from '\d+$'),todo_id
from br3001_analysis_category.matching_interpolate
4) If address like "117 OLD FARM RD" then output is "RD"
means from Last space value is require
a) substring(todo_title,'[A-Z]+$')
b) regexp_replace("todo_title",'(.*)( .*?)','\2')
c) split_part("todo_title" ,' ',array_length(regexp_split_to_array("todo_title" ,' '),1))
5) If address like "117 OLD FARM RD" then output is "117 OLD FARM"
means from Last space value is not require
a) regexp_replace("todo_title",'(.*)( .*?)','\1')
6)
a) If address like "117 OLD FARM RD" then output is "OLD FARM RD"
substring(todo_title,'^[0-9]+(.*)')
b)If address like "117A OLD FARM RD" then output is "OLD FARM RD"
substring(todo_title,'[0-9A-Z]+(.*)')
c) If address like "117 OLD FARM RD" then output is "FARM RD"
substring(todo_title,'[A-Z]+(.*)')
d) If address like "117 Old Farm Rd" then output is "Farm Rd"
substring(todo_title,'[A-Za-z]+(.*)')
7) If address like "OLD FARM RD" then output is "OLD"
or address like "117 OLD FARM RD" then output is "117"
substring(todo_title,'(^.*?) .*$')
Last chracter till 3 words:-
if value like this "Matching in range" and value if you want "nge" then
substring(todo_title,'[A-Za-z]{1,3}$')
if value like this "117 OLD FARM RD" and value nothing came if last word less than 3
substring(todo_title,'[A-Za-z]{3}$')
8)Position Query
a)if value like "Matching with no address house number in range" and output require
"Matching to CL "
SUBSTR(todo_descr,0,position('house' in todo_descr))
or
split_part(todo_descr,'house',1)
b)if value like "Matching with no address house number in range" and output require
"in range"
SUBSTR(todo_descr,position('house number' in todo_descr)+13)
or
split_part(todo_descr,'house number',2)
9)/* Replace Function */
if value like "Matching with no address house number in range" and output require
"Matching with no address Hno in range"
10) Bittrim function(Remove unwanted space in starting and last:-
btrim(combined_address)
example:-
update br23002_feb6.poi_data set combined_address=btrim(combined_address)
11) /* one letter from starting */
address like "117 OLD FARM RD" then output is "1"
(substring(combined_address,'\w{1}'))
Example:-
select (substring(combined_address,'\w{1}')),combined_address
from br12001_qc.mail_additional_source_match_final
where combined_address ~ '^[A-Z]{1}$'
12) /* first letter numbers */
address like "117 OLD FARM RD" then output is "117"
(substring(combined_address,'\w+'))
Example:-
select combined_address,streetname,zipcode,state,city,county_code,dpv_confirm,hsn,hsn_prefix,hsn_suffix,full_hsn
from colorado.br36002_wb
where combined_address ~ '^[A-Z]{1}[0-9]+$'
13) /* just letters */
address like "1020 GUSTAFSON AVE 24060" then output is "GUSTAFSON AVE"
(substring(combined_address,'\D+'))
Example:- select (substring(combined_address,'\D+')),combined_address
from br12001_qc.mail_additional_source_match_final where combined_address ~ '^[A-Z]+$'
17) Intersect query based on attribute
Example:-
select * from br3004_analysis.address_point_br3004_apt where address in(
(SELECT address FROM br3004_analysis.address_point_br3004_apt WHERE pref_conn='-' or prefix is not null)
intersect
(select address from br3004_analysis.address_point_br3004_apt where prefix is null))
18) Identify Duplicate value based on multiple value which are intersecting with 10 M buffer of any other layer Using Over Partition
Example:-
Update br27009.detection_todo_68233
set is_duplicate_flag='Y' where is_duplicate_flag is null and todo_id in
(select todo_id from
(select ROW_NUMBER() OVER( PARTITION BY b.parcel_id,a.house_number order by b.parcel_id,a.house_number, a.feature_id asc) row_num ,a.ticket_id
from br27009.detection_todo_68233 a, br27009.cl_parcel b
where ST_Intersects(a.pt_buff10m, b.geom) ) t where t.row_num > 1 )
19)Change column name OF TABLE:-
alter table poi_vendor_data_latest.opentable Rename column "Address" to address
20) Make line between two point:-(-- Hooklines)
st_makeline(a.geom, b.geom)
Example:1:-
create table analysis_br23002.line as
select a.* ,st_makeline(a.geom, b.geom)as geom,round(ST_Distance(a.geom::geography, b.geom::geography)::numeric, 2) as length_m,b.hsn_street as cl_street from analysis_br23002.mail_interpolate a ,analysis_br23002.unmatched_primary b
where a.hsn_street=b.hsn_street and ST_DWithin(a.geom, b.geom, 0.0020)
Example2:-
create table b5.hook_lines as
select t4.*
from
(select t1.feature_id, ST_AsText(t1.geom) as location, t1.segment_id, t1.house_num, t1.street as street_apt, t3.full_name as street_road, t2.geom as end_point,
round(ST_Distance(t1.geom::geography, t2.geom::geography)::numeric, 2) as length_m, ST_Makeline(t1.geom, t2.geom) as geom
from b5.address_points t1, b5.access_points t2, b5.road_segments t3
where t2.access_type = 'ADDRESS' and
t1.feature_id = t2.feature_id and
t2.segment_id = t3.feature_id
union all
select t1.feature_id, 'POINT('||t1.reppt_x||' '||t1.reppt_y||')' as location, t1.segment_id, t1.house_num, t1.street as street_apt, t3.full_name as street_road, t2.geom as end_point,
round(ST_Distance(ST_SetSRID(ST_MakePoint(t1.reppt_x, t1.reppt_y), 4326)::geography, t2.geom::geography)::numeric, 2) as length_m,
ST_Makeline(ST_SetSRID(ST_MakePoint(t1.reppt_x, t1.reppt_y), 4326), t2.geom) as geom
from b5.address_parcels t1, b5.access_points t2, b5.road_segments t3
where t2.access_type = 'ADDRESS' and
t1.feature_id = t2.feature_id and
t2.segment_id = t3.feature_id) t4;
21) a) Copy Schema from my local to server:-
pg_dump -C -U rachin_jain -d postgres -n poi_br23009 | psql -h 17.76.43.67 -U rachin_jain -d prc_addressing_db -p 5433
Then reassign access to user:-
Example:-
reassign owned by rachin_jain to du_addressdb;
b) Copy schema From server to my local:
pg_dump -C -h 17.76.56.63 -U rachin_jain -d postgres -n poi_br23009 | psql -U rachin_jain -d postgres
22)Unnest Function:- (Splitting range records or / having records in multiple records.
unnest(string_to_array(segments, '/'))
If single feature id having value 123456/987546,then this commands create 2 records
in table with other column with same records.
Example:-
create table br27009.address_range_final as
select *,unnest(string_to_array(segments, '/')) segment_id from br27009.address_range_with_slash
23) delete records from table:-
Example:-
Delete from poi_sources.poi_br23009;
commit;
OR
Truncate table br3001.cl_in_mail_format
Based on attribute
delete from br12001_qc.unmatched_primary where combined_address in (select combined_address from br12001_qc.delete_unmatched_primary_records )
24)st_geometryN(geom, 1) FUNCTION RETURN THE TYPE OF GEOMATRY AS A STRING.
alter table final_br11006_28.match_supressed alter column geom type geometry(point,4326) using st_geometryN(geom, 1);
25) Insert into command:-
Example:-
insert into br27009.address_range_final (gid,feature_id,l_a_parity,l_a_from,l_a_to,l_a_unnum,r_a_from,r_a_to,r_a_unnum, segments,name,geom,segment_id)
select gid,feature_id,l_a_parity,l_a_from,l_a_to,l_a_unnum,r_a_from,r_a_to,r_a_unnum, segments,name,geom::geometry,feature_id from
br27009.address_range_final where segments is null;
26) Update function :-
Example1:-
update br4004.cl_parcels_with_bldg_n_mail_info as t1
set mail_all = t2.count
from (select gid, count(*)
from br4004.mail_points_within10m_of_cl_parcels
group by gid) t2
where t1.gid = t2.gid;
Example2:-
update br4004.cl_parcels_with_bldg_n_mail_info as t1
set mail_geom = t2.mail_geom,
mail_hsn_full = t2.full_hsn,
mail_addr = t2.official_a
from br4004.mail_points_within10m_of_cl_parcels t2
where t1.gid = t2.gid
Example3:-
update nmdr.match_ingest_poi_addr a
set feature_id = b.feature_id,
prefix = b.prefix,
pref_conn = b.pref_conn,
house_num = b.house_num,
from nmdr.address_points b
where a.feature_id is null and
a.hsn = b.house_num and
st_dwithin(a.geom, b.geom, 0.0002)
Eaxmple4:-
update nmdr.match_ingest_poi_addr a
set vee_exists = true
where feature_id in (select distinct feature_id
from nmdr.access_points
where access_typ = 'VEHICLE_ENTRY')
27)Unit value updating in column :-
row_number() over(order by geom) as serial_no
create table poi_vendor_data_latest.merge_poi_source_final1 as
Select row_number() over(order by geom) as serial_no,* from poi_vendor_data_latest.merge_poi_source_final
or
alter table poi_vendor_data_latest.merge_poi_source_final add column gid serial
28) Copy schema From local to server:
/Applications/Postgres.app/Contents/Versions/10/bin/pg_dump -C -U ezhilmathi_porchezhian -h localhost -d postgres -n s18 | psql -h 17.76.43.67 -p 5433 -U ezhilmathi_porchezhian -d prc_addressing_db
copy schema From server to local:
/Applications/Postgres.app/Contents/Versions/10/bin/pg_dump -C -h 17.76.43.67 -p 5433 -U ezhilmathi_porchezhian -d prc_addressing_db -n indiana_22_jan | psql -U ezhilmathi_porchezhian -h localhost -d postgres
29) /* filters out data */
1)/* one letter */
PriDelvNo ~ '^[A-Z]{1}$'
example:-
UPDATE colorado.br36002_wb
SET hsn = (substring(PriDelvNo,'\w{1}'))
WHERE PriDelvNo ~ '^[A-Z]{1}$'
2)/* letter numbers */
PriDelvNo ~ '^[A-Z]{1}[0-9]+$'
example:-
UPDATE colorado.br36002_wb
SET hsn = (substring(PriDelvNo,'\w+'))
WHERE PriDelvNo ~ '^[A-Z]{1}[0-9]+$'
3)/* just letters */
PriDelvNo ~ '^[A-Z]+$'
4) /* letter numbers letter */
PriDelvNo ~ '^[A-Z]{1}[0-9]+[A-Z]{1}$'
5)/* numbers + letters */
PriDelvNo ~ '^[0-9]{2}[A-Z]{2}$'
6)/* number letter - number*/
PriDelvNo ~ '^[0-9]{1}[A-Z]+[-]{1}[0-9]{1}$'
7)/* letter - number letter*/
PriDelvNo ~ '^[A-Z]{1}[-]{1}[0-9]{1}[A-Z]{1}$'
example:-
UPDATE colorado.br36002_wb
SET hsn = (substring(PriDelvNo,'\w+'))
WHERE PriDelvNo ~ '^[A-Z]{1}[-]{1}[0-9]{1}[A-Z]{1}$'
8) /* numbers + letter*/
PriDelvNo ~ '^[0-9]+[A-Z]{1}$'
9) /* numbers letter number */
PriDelvNo ~ '^[0-9]+[A-Z]{1}[0-9]{1}$'
10)/* numers - numbers */
PriDelvNo ~ '^[0-9]+[-]{1}[0-9]+$'
30) Delete wrong geom from any layer
DELETE from multipolygons where ST_IsValid(geom)=false;
1) Loading Shp into Postgress(Local):-
->Run below command in terminal.
Shp2pgsql -I -D -s 4326 '/Users/rachin_jain/Documents/Detection_Data/BR27009/Unmatched_mail_data/Address_issue_todo.shp'
br27009.unmatched_wave_todo | psql -d postgres
b) Copy Data from csv to postgress in local system :-
copy poi_br23009.poi from '/Users/rachin_jain/Documents/mail_creation/POI_DATA/23009/123/poi_23009_final_upload.csv' HEADER DELIMITER ',' CSV;
c)Copy Data from csv to postgress in server :-
psql -h 17.76.43.67 -p 5433 -U rachin_jain -d prc_addressing_db -c "\copy poi_vendor_data_latest.br12001_poi_final from '/Users/rachin_jain/Documents/mail_creation/POI_DATA/12001/BR12001.csv' HEADER DELIMITER ',' CSV;"
d) Loading Shp into Postgress(Server):-
shp2pgsql -I -D -s 4326 '/Users/rachin_jain/Downloads/BR9002/files/BR90022018-11-06a/BR9002_06112018_0_UNMATCHED_PRIMARY.shp' br17031.unmatched_primary | psql -h 19.76.23.67 -p 5433 -d prc_addressing_db -U rachin_jain
e) Download shp from Postgress(Server) :-
pgsql2shp -f /Users/rachin_jain/Documents/Analysis_for_coverage/MAIL_data/test.shp -h 19.76.53.67 -P password123 -p 5433 -d prc_addressing_db br24005.br24005_poi
f) Download shp from Postgress(local) :-
pgsql2shp -f /Users/rachin_jain/Documents/Analysis_for_coverage/MAIL_data/test.shp -h localhost -P postgres -p 5432 -d postgres br3001.br3001_cl_parcel
g)example to load geopackage in postgress
Following is a command-line example to load geopackage (.gpkg) data to PostGIS database.
ogr2ogr -f PostgreSQL "PG:user=rachinj dbname=Geocoding schemas=uk" /Users/rachin_jain/Desktop/UK/Difference/Birimingham/Differ_data_xyz.gpkg
2) Add Geometry column & update geom:-
SELECT AddGeometryColumn ('br27009','unmatched_wave_todo','geom',4326,'POINT',2);
update br27009.unmatched_wave_todo
set geom = ST_SetSRID(ST_MakePoint(lon::float, lat::float), 4326);
Add multiple column having default value:-
Example:-
alter table b5.buildings
add column house_num varchar(255),
add column hno_is_dup bool default(false),
add column is_dup bool default(false),
add column is_in_repo bool default(false);
3) Index Query for Geometry column:-
CREATE INDEX detection_geom_sidx
ON br27009.unmatched_wave_todo USING gist (geom);
Drop INDEX br27009.MATCH_INGEST_hsn_idx1;
CREATE INDEX MATCH_INGEST_hsn_idx1 ON br27009.unmatched_wave_todo(hsn);
4) Cast Command if geomatry not updating:-
CAST(nullif(long, '') AS float )
CAST(nullif(lat, '') AS float )
Example:-
update br23008_mail_build.interpolate_23008 set long = CAST(nullif(long, '') AS float )
update br23008_mail_build.interpolate_23008 set lat = CAST(nullif(lat, '') AS float )
update br23008_mail_build.interpolate_23008
set geom = ST_SetSRID(ST_MakePoint(long::float, lat::float), 4326);
buffer Query 10 mtr----
alter table br27009.unmatched_wave_todo
Add column pt_buff10m geometry
Update br27009.unmatched_wave_todo
Set pt_buff10m = st_buffer(geom, 0.0001)
for 0.5 mtr buffer example
alter table br27009.unmatched_wave_todo
Add column pt_buff_0_5m geometry
Update br27009.unmatched_wave_todo
Set pt_buff_0_5m = st_buffer(geom, 0.000005)
For 20 mtr:-st_buffer(geom, 0.0002)
For 50 mtr:-st_buffer(geom, 0.0005)
For 100 mtr:-st_buffer(geom, 0.0010)
5) Intersect query spatially :-
st_intersects(a.geom,b.geom)
create table br27009.Detection_Todos_with20m_CL_parcel_with_matching_HSN
as
select a.ticket_id,a.todo_id,st_astext(a.geom),a.todo_descr,a.todo_title,a.house_number,b.addr,b.parcel_id,b.zip,b.house_num_cl
from
br27009.detection_todo_after_remove_matching_with_cl_parcel a,
br27009.CL_parcel_after_exclude_match_ingest_parcel b
where st_intersects(a.pt_buff20m,b.geom) and a.house_number=b.house_num_cl
6) For Checkinh Stack point of same table:-
alter table br27009.detection_todo_after_remove_null_featureid_47596
Add column pt_buff_0_5m geometry
Update br27009.detection_todo_after_remove_null_featureid_47596
Set pt_buff_0_5m = st_buffer(geom, 0.000005)
create table br27009.stack_detection_todo as
select a.* from br27009.detection_todo_after_remove_null_featureid_47596 a,
br27009.detection_todo_after_remove_null_featureid_47596 b
where st_intersects(a.pt_buff_0_5m,b.geom) and a.house_number!=b.house_number
7) Cluster Query:-
ST_ClusterWithin(geom,0.0005)
create table br16006.br16006_building_buff as
SELECT generate_subscripts((ST_ClusterWithin(geom,0.0005)),1) gid,
ST_Buffer(unnest(ST_ClusterWithin(geom,0.0005)),0.0002) geom,
ST_NumGeometries( unnest(ST_ClusterWithin(geom,0.0005))) bldg_count
FROM br16006.br16006_building;
8) Case Function:-
case when prefix is null then 'np' else 'p' end
Example:-1
update br27009.unmatched_wave_todo_136954
set is_detection_flag = CASE WHEN (todo_descr like '% detection%') THEN 'Y' ELSE 'N' END;
Example:-2
update br3003_analysis.cl_parcel_queens set is_flag =case when
(parcel_id
in(select parcel_id from br3003_analysis.address_point_in_cl_parcel)) then 'Yes' else 'No' end
Example:-3
update br3004_analysis.temp
set low_count = (case when np_count>p_count then 'p_count' else 'np_count' end);
Example:-4
update br3004_analysis.temp a
set p_count = b.p_count
from
( select address, (case when prefix is null then 'np' else 'p' end) as pref, count(*) as p_count
from br3004_analysis.roadsegment_having_prefix_connector_mixed_apt
where prefix is not null
group by address, pref) b
where a.address = b.address;
9) Remove duplicate Value based on column:-
delete from br3001.todo_falling_in_range_3node_base where ctid in (
SELECT UNNEST(ARRAY_REMOVE(all_ctids,actid))
FROM (
SELECT
MIN(b.ctid) AS actid,
ARRAY_AGG(ctid) AS all_ctids
FROM br3001.todo_falling_in_range_3node_base b
GROUP BY todo_id
HAVING COUNT(*) > 1) c);
10) Calculate Start Point and end point geomatry of line :-
ST_StartPoint(ST_LineMerge(geom))as geom
ST_EndPoint(ST_LineMerge(geom)) as geom
Example:-
select from_node as node, ST_StartPoint(ST_LineMerge(geom))as geom
from br3001."road_segment_BR_3001"
union all
select to_node as node, ST_EndPoint(ST_LineMerge(geom)) as geom
from br3001."road_segment_BR_3001"
11) Even Odd Function:-
Even= CAST(nullif(house_number, '') AS integer) %2 = 0;
Odd= CAST(nullif(house_number, '') AS integer) %2 != 0;
12) Like ANY Array Function:-
LIKE ANY(ARRAY['%1%','%2%','%3%','%4%','%5%','%6%','%7%','%8%','%9%','%0%'])
LIKE ANY(ARRAY['% NW%','% NE%','% SW%','% SE%','% N','% E','% W','% S'])
NOT LIKE ALL (ARRAY['NW','NE','SW','SE','N','E','W','S'])
Example:-
select btrim(regexp_replace("street",'(.*)( .*?)','\2')),street from br23009_feb22_deliverables6.br23009_match_ingest1
where btrim(regexp_replace("street",'(.*)( .*?)','\2')) not in (select suffix_form FROM spec.abbreviations)
and btrim(regexp_replace("street",'(.*)( .*?)','\2')) NOT LIKE ALL (ARRAY['NW','NE','SW','SE','N','E','W','S'])
13) Buffer and dissolve Query:-
ST_Union(ST_Buffer(geom, 0.0015))
Example:-
drop table if exists b5.buildings_dup_buffer_dissolve;
create table b5.buildings_dup_buffer_dissolve as
select house_num, ST_Union(ST_Buffer(geom, 0.0015)) as geom
from b5.buildings where is_dup = true
---Create duplicates buffer and dissolve ---- Single part
drop table if exists b5.buildings_dup_buffer_single_part;
create table b5.buildings_dup_buffer_single_part as
select house_num, (ST_Dump(geom)).geom as geom
from b5.buildings_dup_buffer_dissolve;
14) Distance Query:-
Function:- ST_Distance(a.geom::geography, b.geom::geography)
Function:- ST_DWithin(a.geom, b.geom, 0.0015)
Example1:- (200 Mtr)
select a.*,b.* from analysis_br23003.unmatched_primary a , analysis_br23003.mail_interpolate b where
a.hsn_street = b.hsn_street and
ST_DWithin(a.geom, b.geom, 0.0020)
Example2:-
update nmdr.match_ingest_poi_addr a
set feature_id_tm = b.feature_id,
address_tm = b.address,
distance = round(st_distance(a.geom::geography, b.geom::geography)::numeric, 2)
from nmdr.address_points b where
upper(a.cl_address_ordinal_mod) = upper(b.address);
Points within parcels:-
Function:- ST_Within(a.geom, b.geom)
Example:-
create table br4004.mail_points_within_cl_parcels as
select a.*, b.geom_repo as mail_geom
from br4004.cl_parcels a, br4004.mail2 b
where ST_Within(a.geom, b.geom)
15) Copy table data into CSV file:-
copy (select row_number() over(order by cluster_id, s_no, street, house_num) as sl_no, feature_id, house_num, street, location, is_duplicate
from b5.buildings_not_in_repo2
order by cluster_id, s_no, street, house_num)
to '/Users/rachin_jain/Documents/b5/Validation/buildings_not_in_repo.csv' with csv header delimiter ',';
Copy table data from server into csv file:-
psql -h 17.76.43.67 -p 5433 -U rachin_jain -d prc_addressing_db -c "\copy (select id,gid,longitude,latitude,poi_vendor_id,poi_name,address,city_name,state_code,postal_code,country,poi_vendor_name,business_category,modified_address,fips,postal_code_final,standardized_address
from poi_vendor_data_latest.br24003_poi) to '/Users/rachin_jain/Documents/mail_creation/POI_DATA/BR24003_POI.csv'with csv header delimiter ','"
16) Substring Function :-
1)if address like "009 AB Road" then output is "9"
house_number=TRIM(LEADING '0' FROM substring(column_Name from '^[0-9]+'))
2)
A) if address like "009 AB Road" then output is "009"
house_number=substring(column_Name from '^[0-9]+')
house_number=substring(feature_id from '^\d+$')
B) if address like "119A E 4TH ST" then output is "119A"
house_number=substring(todo_title,'^[0-9A-Z]+')
3) If Value like F-100234444000 THEN output is "100234444000"
a) right(feature_id ,length(feature_id)-2)
b) substring(feature_id from '[0-9]+')
c) substring(feature_id from '\d+$')
Example:- select substring(todo_id from '\d+$'),todo_id
from br3001_analysis_category.matching_interpolate
4) If address like "117 OLD FARM RD" then output is "RD"
means from Last space value is require
a) substring(todo_title,'[A-Z]+$')
b) regexp_replace("todo_title",'(.*)( .*?)','\2')
c) split_part("todo_title" ,' ',array_length(regexp_split_to_array("todo_title" ,' '),1))
5) If address like "117 OLD FARM RD" then output is "117 OLD FARM"
means from Last space value is not require
a) regexp_replace("todo_title",'(.*)( .*?)','\1')
6)
a) If address like "117 OLD FARM RD" then output is "OLD FARM RD"
substring(todo_title,'^[0-9]+(.*)')
b)If address like "117A OLD FARM RD" then output is "OLD FARM RD"
substring(todo_title,'[0-9A-Z]+(.*)')
c) If address like "117 OLD FARM RD" then output is "FARM RD"
substring(todo_title,'[A-Z]+(.*)')
d) If address like "117 Old Farm Rd" then output is "Farm Rd"
substring(todo_title,'[A-Za-z]+(.*)')
7) If address like "OLD FARM RD" then output is "OLD"
or address like "117 OLD FARM RD" then output is "117"
substring(todo_title,'(^.*?) .*$')
Last chracter till 3 words:-
if value like this "Matching in range" and value if you want "nge" then
substring(todo_title,'[A-Za-z]{1,3}$')
if value like this "117 OLD FARM RD" and value nothing came if last word less than 3
substring(todo_title,'[A-Za-z]{3}$')
8)Position Query
a)if value like "Matching with no address house number in range" and output require
"Matching to CL "
SUBSTR(todo_descr,0,position('house' in todo_descr))
or
split_part(todo_descr,'house',1)
b)if value like "Matching with no address house number in range" and output require
"in range"
SUBSTR(todo_descr,position('house number' in todo_descr)+13)
or
split_part(todo_descr,'house number',2)
9)/* Replace Function */
if value like "Matching with no address house number in range" and output require
"Matching with no address Hno in range"
10) Bittrim function(Remove unwanted space in starting and last:-
btrim(combined_address)
example:-
update br23002_feb6.poi_data set combined_address=btrim(combined_address)
11) /* one letter from starting */
address like "117 OLD FARM RD" then output is "1"
(substring(combined_address,'\w{1}'))
Example:-
select (substring(combined_address,'\w{1}')),combined_address
from br12001_qc.mail_additional_source_match_final
where combined_address ~ '^[A-Z]{1}$'
12) /* first letter numbers */
address like "117 OLD FARM RD" then output is "117"
(substring(combined_address,'\w+'))
Example:-
select combined_address,streetname,zipcode,state,city,county_code,dpv_confirm,hsn,hsn_prefix,hsn_suffix,full_hsn
from colorado.br36002_wb
where combined_address ~ '^[A-Z]{1}[0-9]+$'
13) /* just letters */
address like "1020 GUSTAFSON AVE 24060" then output is "GUSTAFSON AVE"
(substring(combined_address,'\D+'))
Example:- select (substring(combined_address,'\D+')),combined_address
from br12001_qc.mail_additional_source_match_final where combined_address ~ '^[A-Z]+$'
17) Intersect query based on attribute
Example:-
select * from br3004_analysis.address_point_br3004_apt where address in(
(SELECT address FROM br3004_analysis.address_point_br3004_apt WHERE pref_conn='-' or prefix is not null)
intersect
(select address from br3004_analysis.address_point_br3004_apt where prefix is null))
18) Identify Duplicate value based on multiple value which are intersecting with 10 M buffer of any other layer Using Over Partition
Example:-
Update br27009.detection_todo_68233
set is_duplicate_flag='Y' where is_duplicate_flag is null and todo_id in
(select todo_id from
(select ROW_NUMBER() OVER( PARTITION BY b.parcel_id,a.house_number order by b.parcel_id,a.house_number, a.feature_id asc) row_num ,a.ticket_id
from br27009.detection_todo_68233 a, br27009.cl_parcel b
where ST_Intersects(a.pt_buff10m, b.geom) ) t where t.row_num > 1 )
19)Change column name OF TABLE:-
alter table poi_vendor_data_latest.opentable Rename column "Address" to address
20) Make line between two point:-(-- Hooklines)
st_makeline(a.geom, b.geom)
Example:1:-
create table analysis_br23002.line as
select a.* ,st_makeline(a.geom, b.geom)as geom,round(ST_Distance(a.geom::geography, b.geom::geography)::numeric, 2) as length_m,b.hsn_street as cl_street from analysis_br23002.mail_interpolate a ,analysis_br23002.unmatched_primary b
where a.hsn_street=b.hsn_street and ST_DWithin(a.geom, b.geom, 0.0020)
Example2:-
create table b5.hook_lines as
select t4.*
from
(select t1.feature_id, ST_AsText(t1.geom) as location, t1.segment_id, t1.house_num, t1.street as street_apt, t3.full_name as street_road, t2.geom as end_point,
round(ST_Distance(t1.geom::geography, t2.geom::geography)::numeric, 2) as length_m, ST_Makeline(t1.geom, t2.geom) as geom
from b5.address_points t1, b5.access_points t2, b5.road_segments t3
where t2.access_type = 'ADDRESS' and
t1.feature_id = t2.feature_id and
t2.segment_id = t3.feature_id
union all
select t1.feature_id, 'POINT('||t1.reppt_x||' '||t1.reppt_y||')' as location, t1.segment_id, t1.house_num, t1.street as street_apt, t3.full_name as street_road, t2.geom as end_point,
round(ST_Distance(ST_SetSRID(ST_MakePoint(t1.reppt_x, t1.reppt_y), 4326)::geography, t2.geom::geography)::numeric, 2) as length_m,
ST_Makeline(ST_SetSRID(ST_MakePoint(t1.reppt_x, t1.reppt_y), 4326), t2.geom) as geom
from b5.address_parcels t1, b5.access_points t2, b5.road_segments t3
where t2.access_type = 'ADDRESS' and
t1.feature_id = t2.feature_id and
t2.segment_id = t3.feature_id) t4;
21) a) Copy Schema from my local to server:-
pg_dump -C -U rachin_jain -d postgres -n poi_br23009 | psql -h 17.76.43.67 -U rachin_jain -d prc_addressing_db -p 5433
Then reassign access to user:-
Example:-
reassign owned by rachin_jain to du_addressdb;
b) Copy schema From server to my local:
pg_dump -C -h 17.76.56.63 -U rachin_jain -d postgres -n poi_br23009 | psql -U rachin_jain -d postgres
22)Unnest Function:- (Splitting range records or / having records in multiple records.
unnest(string_to_array(segments, '/'))
If single feature id having value 123456/987546,then this commands create 2 records
in table with other column with same records.
Example:-
create table br27009.address_range_final as
select *,unnest(string_to_array(segments, '/')) segment_id from br27009.address_range_with_slash
23) delete records from table:-
Example:-
Delete from poi_sources.poi_br23009;
commit;
OR
Truncate table br3001.cl_in_mail_format
Based on attribute
delete from br12001_qc.unmatched_primary where combined_address in (select combined_address from br12001_qc.delete_unmatched_primary_records )
24)st_geometryN(geom, 1) FUNCTION RETURN THE TYPE OF GEOMATRY AS A STRING.
alter table final_br11006_28.match_supressed alter column geom type geometry(point,4326) using st_geometryN(geom, 1);
25) Insert into command:-
Example:-
insert into br27009.address_range_final (gid,feature_id,l_a_parity,l_a_from,l_a_to,l_a_unnum,r_a_from,r_a_to,r_a_unnum, segments,name,geom,segment_id)
select gid,feature_id,l_a_parity,l_a_from,l_a_to,l_a_unnum,r_a_from,r_a_to,r_a_unnum, segments,name,geom::geometry,feature_id from
br27009.address_range_final where segments is null;
26) Update function :-
Example1:-
update br4004.cl_parcels_with_bldg_n_mail_info as t1
set mail_all = t2.count
from (select gid, count(*)
from br4004.mail_points_within10m_of_cl_parcels
group by gid) t2
where t1.gid = t2.gid;
Example2:-
update br4004.cl_parcels_with_bldg_n_mail_info as t1
set mail_geom = t2.mail_geom,
mail_hsn_full = t2.full_hsn,
mail_addr = t2.official_a
from br4004.mail_points_within10m_of_cl_parcels t2
where t1.gid = t2.gid
Example3:-
update nmdr.match_ingest_poi_addr a
set feature_id = b.feature_id,
prefix = b.prefix,
pref_conn = b.pref_conn,
house_num = b.house_num,
from nmdr.address_points b
where a.feature_id is null and
a.hsn = b.house_num and
st_dwithin(a.geom, b.geom, 0.0002)
Eaxmple4:-
update nmdr.match_ingest_poi_addr a
set vee_exists = true
where feature_id in (select distinct feature_id
from nmdr.access_points
where access_typ = 'VEHICLE_ENTRY')
27)Unit value updating in column :-
row_number() over(order by geom) as serial_no
create table poi_vendor_data_latest.merge_poi_source_final1 as
Select row_number() over(order by geom) as serial_no,* from poi_vendor_data_latest.merge_poi_source_final
or
alter table poi_vendor_data_latest.merge_poi_source_final add column gid serial
28) Copy schema From local to server:
/Applications/Postgres.app/Contents/Versions/10/bin/pg_dump -C -U ezhilmathi_porchezhian -h localhost -d postgres -n s18 | psql -h 17.76.43.67 -p 5433 -U ezhilmathi_porchezhian -d prc_addressing_db
copy schema From server to local:
/Applications/Postgres.app/Contents/Versions/10/bin/pg_dump -C -h 17.76.43.67 -p 5433 -U ezhilmathi_porchezhian -d prc_addressing_db -n indiana_22_jan | psql -U ezhilmathi_porchezhian -h localhost -d postgres
29) /* filters out data */
1)/* one letter */
PriDelvNo ~ '^[A-Z]{1}$'
example:-
UPDATE colorado.br36002_wb
SET hsn = (substring(PriDelvNo,'\w{1}'))
WHERE PriDelvNo ~ '^[A-Z]{1}$'
2)/* letter numbers */
PriDelvNo ~ '^[A-Z]{1}[0-9]+$'
example:-
UPDATE colorado.br36002_wb
SET hsn = (substring(PriDelvNo,'\w+'))
WHERE PriDelvNo ~ '^[A-Z]{1}[0-9]+$'
3)/* just letters */
PriDelvNo ~ '^[A-Z]+$'
4) /* letter numbers letter */
PriDelvNo ~ '^[A-Z]{1}[0-9]+[A-Z]{1}$'
5)/* numbers + letters */
PriDelvNo ~ '^[0-9]{2}[A-Z]{2}$'
6)/* number letter - number*/
PriDelvNo ~ '^[0-9]{1}[A-Z]+[-]{1}[0-9]{1}$'
7)/* letter - number letter*/
PriDelvNo ~ '^[A-Z]{1}[-]{1}[0-9]{1}[A-Z]{1}$'
example:-
UPDATE colorado.br36002_wb
SET hsn = (substring(PriDelvNo,'\w+'))
WHERE PriDelvNo ~ '^[A-Z]{1}[-]{1}[0-9]{1}[A-Z]{1}$'
8) /* numbers + letter*/
PriDelvNo ~ '^[0-9]+[A-Z]{1}$'
9) /* numbers letter number */
PriDelvNo ~ '^[0-9]+[A-Z]{1}[0-9]{1}$'
10)/* numers - numbers */
PriDelvNo ~ '^[0-9]+[-]{1}[0-9]+$'
30) Delete wrong geom from any layer
DELETE from multipolygons where ST_IsValid(geom)=false;
No comments:
Post a Comment