Using foreign data wrapper to use PostGIS with SQLServer
Posted by smathermather on May 29, 2016
Here was the problem that needed solved last week (we have a few similar problems in upcoming projects, so this was an exciting thing to try): we needed to use PostGIS to access data in a SQLServer database. The SQLServer database backs the web site in question, the underlying content management system, etc., so no– removing SQLServer isn’t really an option at this stage. Obviously PostGIS is a requirement too… .
Before I go further, I used tds_fdw as the foreign data wrapper. The limitations here are as follows: it is a read-only connection, and only works with Sybase and SQLServer, as it uses tabular data stream protocol for communicating between client and server. This is not as generic a solution as we can use. Next time I’ll try ogr_fdw which is more generic as it can connect with other databases and other data types. Another advantage to ogr_fdw is we can use IMPORT FOREIGN SCHEMA. Regina Obe and Leo Hsu warn though to limit this to 150 tables or so for performance reasons.
With the limitations listed above, this is how we built the thang:
DROP SERVER beach_fdw CASCADE; -- Create the server connection with FOREIGN DATA WRAPPER CREATE SERVER beach_fdw FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'name_or_ip', port '1433', database 'non-spatial-database', tds_version '7.1', msg_handler 'notice'); -- We map the postgres user to the user that can read the table we're interested in CREATE USER MAPPING FOR postgres SERVER beach_fdw OPTIONS (username 'user', password 'password'); -- Create the actual foreign table connection CREATE FOREIGN TABLE beach_closures ( AutoNumber int NOT NULL, Title varchar NOT NULL, StartDate timestamp without time zone NOT NULL, WaterQuality varchar NOT NULL, Latitude varchar NOT NULL, Longitude varchar NOT NULL, BeachStatus varchar NOT NULL, ClosureColor varchar NOT NULL) SERVER beach_fdw OPTIONS (schema_name 'schema_name', table_name 'vw_CMPBeachClosures'); -- Now we create a spatial view using our longitude and latitude CREATE VIEW v_beach_closures AS SELECT AutoNumber, Title, StartDate, WaterQuality, Latitude, Longitude, BeachStatus, ClosureColor, ST_SetSRID(ST_MakePoint(Longitude::numeric, Latitude::numeric), 4326) AS geom FROM beach_closures;
Voila! A nice little PostGIS enabled view of a SQLServer view or table!