postgresql의 geometry 함수(st_point, st_makeline, st_setstrid), lead함수(python으로 하는것도)

postgresql_1.md

ST_SetSRID, geometry 컬럼에 SRID 지정

ST_SetSRID(st_point(start_x ::double precision, start_y::double precision), 4326)

ST_Transdorm, SRID 변경

query_set_srid = "ALTER TABLE table_nm \
                    ALTER COLUMN geom TYPE geometry(point, 5179)\
                    USING ST_Transform(ST_SetSRID(geom, 4326), 5179)"
                    

ST_MakeLine, geom 2개(point 2개)로 line geom 만들기

ST_MakeLine(geom1, geom2)

lead, 다음행 끌어올리기

select route_id, station_order, station_id ,station_nm, y, x,  
lead(station_id, 1) over(partition by route_id order by cast(station_order as numeric)) as "next_station_id",
lead(station_nm, 1) over(partition by route_id order by cast(station_order as numeric)) as "next_station_nm",
lead(y, 1) over(partition by route_id order by cast(station_order as numeric)) as "next_y",
lead(x , 1) over(partition by route_id order by cast(station_order as numeric)) as "next_x"  from table_name;

lead를 python으로....

## lead하기 위해서 재정렬, station_order 내림차순으로 
table_name =  table_name.sort_values(by=['route_nm','station_order'], ascending=False)

## lead 작업 -> shift함수로 행을 열로 올림
table_name['next_station_id'] = table_name.groupby('route_id')['station_id'].shift(1)
table_name['next_station_nm'] = table_name.groupby('route_id')['station_nm'].shift(1)
table_name['next_y'] = table_name.groupby('route_id')['y'].shift(1)
table_name['next_x'] = table_name.groupby('route_id')['x'].shift(1)