0

Question: Is it possible to intersect geometries from No-SQL and SQL databases?

Background: I need to delete the polygons from a PostGIS database that intersects with points (Lat/Long) in a MongoDB.

Workaround: I'm using an FME workbench to make this task, but I would like to do it using Dbeaver or other RMDBS based on a query. Is it possible?

  • You could export the points in mongoDB and import them into a table in postgis and run the intersection from there .. you can use the ST_MakePoint or ST_GeomFromText and ST_PointFromText functions to construct the point geometry in postgis using the exported lat lon from mongoDB. – Jorge Vidinha Aug 27 at 9:39
0

It sounds like a foreign data wrapper may work, but I'm not familiar with MongoDB to know preciselythe best approach. I assume you can make an ODBC connection to the MongoDB with little problem.

I've used ODBC and ogr_fdw (https://github.com/pramsey/pgsql-ogr-fdw) to connect to FireBird tables with X, Y columns (no spatial capability in FireBird, but we had tables and columns with X, Y state plane coordinates for points). I then use a PostGIS query to create point objects from the coordinates and compare them to polygons in PostGIS.

It sounds like a similar approach may work with your MongoDB if you the lat/long in the MongoDB are in separate fields (float/double/real rather than geometry - again I'm not sure the terms used in MongoDB). Here are some other foreign data wrappers: https://wiki.postgresql.org/wiki/Foreign_data_wrappers.

If MongoDB stores geometry objects similar to PostgreSQL, then it may help to use something like ST_X and ST_Y (https://postgis.net/docs/ST_X.html) in a MongoDB query before creating the foreign schema in the steps below.

Here is some example SQL executed in PostgreSQL to connect to a FireBird database. Note that this was on a Windows machine with an ODBC connection setup as 'abc64'. I haven't tested this code specifically, but have used similar code many times.

-----------------------------------------------------------------------------------
-- USING OGR_FDW
-- THIS IS GOOD FOR CONNECTING OUTSIDE POSTGRES: FIREBIRD, ODBC, SHAPEFILES, ETC.
-- EXECUTE THIS CODE IN POSTGRESQL (CAN USE PGADMIN)
-----------------------------------------------------------------------------------

-- CREATE an ogr_fdw extension in PostgreSQL (similar to how PostGIS is an extension)
CREATE EXTENSION ogr_fdw;

-- register a "server" to handle the foreign data connection in PostgreSQL
-- BE SURE DATASOURCE IS SETUP AS A SYSTEM (NOT USER) ODBC DATA SOURCE
-- ALSO BE SURE IT IS NOT A 32-BIT ODBC CONNECTION (atypical, but sometimes done)    
CREATE SERVER abc FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource 'ODBC:abc64');

-- Create a new schema in PostgreSQL that can be accessed by calling it specifically
-- Not specifying schema in PostgreSQL queries will still default to "public"
CREATE SCHEMA IF NOT EXISTS firebird;

-- Import the schema of the foreign table into PostgreSQL
-- Note that ogr_fdw will may add a few columns that can be ignored. 
-- Other foreign data wrappers may or may not do this (postgresql_fdw does not)
IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("table_name") FROM SERVER abc INTO firebird;
-- ALTERNATIVE TO BRING IN ALL TABLES EXCEPT ONE:
IMPORT FOREIGN SCHEMA ogr_all EXCEPT ("table_name") FROM SERVER abc INTO firebird;

-- test that the connection works
SELECT * FROM firebird.table_name;

-- create a geometry object in PostgreSQL
SELECT ST_SetSRID(ST_Point(firebird.table_name.long, firebird.table_name.lat), 4326)

-- find intersections
SELECT id 
  FROM firebird.table_name AS fb
  JOIN public.table_name_2 AS pg
    on ST_Intersects(ST_SetSRID(ST_Point(fb.long, fb.lat), 4326), pg.polygon_geom)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.