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?
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)
