0

I am an absolute beginner in PostgreSQL and PostGIS (databases in general) but have a fairly good working experience in R. I have two multi-polygon data sets of vulnerable areas of India from two different sources - one is around 12gb and it's in .gdb format (let's call it mygdb) and the other is a shapefile around 2gb (let's call it myshp). I want to compare the two sets of vulnerability maps and generate some state-wise measures of fit using intersection (I), difference (D), and union (U) between the maps. I would like to make use of PostGIS functionalities (via R) as neither R (crashes!) nor qgis (too slow) is efficient for this. To start with, I have uploaded both data sets in my PostGIS database. I used ogr2ogr in R to upload mygdb. But I am kind of stuck at this point. My idea is to split both polygon files by states and then apply other functions to get I, U and D. From my search, I think I can use sf functions like st_split, st_intersect, st_difference, and st_union. However, even after splitting, I would imagine that the file sizes will be still too large for r to process, so my questions are

  1. Is my approach the best way forward?
  2. How can I use sf::st_ functions (e.g. st_split, st_intersection) without importing the data from database into R

There are some useful answers to previous relevant questions, like this one for example. But I find it hard to put the steps together from different links and any help with a dummy example would be great. Many thanks in advance.

rm167
  • 1,185
  • 2
  • 10
  • 26
  • Looks like your data is a vector data. If you have loaded it to postrgres with postgis installed, they probably are splitted to different tables (points, lines, polygons) with several rows corresponding to features. There should be no problem loading them part by part to R for comparison. And yes, `sf()` library will be the good choice. Generally speaking, `st_` functions corresponds to `ST_` functions from Postgis. – Grzegorz Sapijaszko Feb 02 '22 at 20:21
  • Thank you. Would you mind providing an example code to, for example, use st_intersection without importing the inputs locally? – rm167 Feb 04 '22 at 13:31

2 Answers2

0

Maybe you could try loading it as a stars proxy. It doesn't load the file to the memory, it applies it directly to the hard drive.

https://r-spatial.github.io/stars/articles/stars2.html

Calleros
  • 83
  • 5
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 02 '22 at 02:43
0

Not answer for question sensu stricte, however in response to request in comment, an example of postgresql/postgis query for ST_Intersection. Based on OSM data in postgresql database imported with osm2pgsql:

WITH 
  highway AS (
    select osm_id, way from planet_osm_line where osm_id = 332054927), 
  dln AS (
    select osm_id, way from planet_osm_polygon where "boundary" = 'administrative' 
    and "admin_level" = '4' and "ref" = 'DS')
SELECT ST_Intersection(dln.way, highway.way) FROM highway, dln
Grzegorz Sapijaszko
  • 1,913
  • 1
  • 5
  • 12