I have approximately 600,000 points that must be joined to ~12 million polygons. This process happens weekly, with the potential to happen daily. The polygon data never changes, and is stored in a remote PostGIS database. The points are currently pushed from R to the remote PostGIS server and ST_DWithin is performed to join polygon attributes to the points. Is there way to keep my points local (i.e. within my R environment) and query the remote polygons?

The goal is to not let highly confidential information (the points) needlessly be copied to a remote server as a temporary table, if I can help it.

  • 1
    So if the polygons never change, why don't you copy them to where the sensitive data is? – Vince Jan 18 at 0:26
  • 2
    This sort of detail really ought to be in the question, but it makes the question moot, because it's not possible to both use an external database and secure the data. – Vince Jan 18 at 1:01
  • 1
    Yes, with a foreign data wrapper. There are a couple of gotchas, which i will write up when back at my desk. – John Powell Jan 18 at 12:51
  • 1
    This would be easier to do with Postgres to Postgres foreign data wrappers, due to the ability to allow spatial indexes to be used on the remote machine. It is still possible from R, but will take longer. Why can you not install Postgres locally? The IT departments of companies that prevent people from doing things that lead to productivity enhancements, ie , install Postgres locally, are not really helping. Postgres, last time I looked, was not a tool commonly used by troll farms. What is their "logic"? – John Powell Jan 18 at 15:32
  • 1
    My experience of foreign data wrappers is using Postgres as the query engine and connecting to remote data sources, whether that by text, another Postgres box, MongoDB, whatever. Your use case is the other way round, you are working locally, in R, and querying a foreign Postgres box, which you don't want the overhead of having to upload data to. Is this correct? – John Powell Jan 21 at 7:49

Your Answer

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

Browse other questions tagged or ask your own question.