Created
January 19, 2018 19:56
-
-
Save slibby/b07a5ed4805a21ac22d752b96edca6e7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- create required extensions if they don't already exist | |
create extension if not exists postgis; | |
create extension if not exists postgres_fdw; | |
--drop remote server if exists | |
drop server if exists sde_host; | |
--create postgres_fdw server | |
create server sde_host foreign data wrapper postgres_fdw options (host 'slibby10.esri.com', dbname 'db_pro_create', port '5432'); | |
--drop user mapping to avoid conflicts | |
drop user mapping if exists for postgres server sde_host; | |
--create user mapping | |
create user mapping for postgres server sde_host options (user 'postgres', password 'postgres'); | |
-- create a new schema to hold our foreign tables (I prefer this, not entirely necessary) | |
create schema if not exists sde_connector; | |
-- import the schema from SDE database | |
import foreign schema connector from server sde_host into sde_connector; | |
--create view in your schema that generates a PostGIS geometry from the WKT text | |
create or replace view sde_connector.remote_locations_view_pg_geom as select *, public.st_geomfromtext(geom_wkt) as shape from sde_connector.remote_locations_view; | |
-- create materialized view to make sure all the data is loaded to your new db rather than dynamically querying | |
create materialized view sde_connector_remote_locations_view_pg_geom_m as select * from sde_connector.remote_locations_view_pg_geom; | |
select geom_wkt as geom_wkt_from_sde, st_geometrytype(shape) as geom_type_in_postgis, shape as postgis_geometry, st_astext(shape) as geom_wkt_from_postgis from sde_connector_remote_locations_view_pg_geom_m; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- create new schema to hold our views | |
create schema if not exists connector; | |
-- Create a view of my point FC (sde.locations) that extracts Esri Geometry to WKT | |
create or replace view connector.locations_wkt as | |
select objectid, sde.ST_AsText(shape)::varchar as geom_wkt from sde.locations; | |
-- Done in SDE! |
Author
slibby
commented
Jan 19, 2018
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment