[postgis-users] OSM and US DEM in PostGIS

[Siddarth Madala]

I was looking for some assistance with the following problem. My use case of PostGIS is to store the United States' OpenStreetMap (OSM) data. This is a solved problem with a significant amount of documentation available. However, my use case differs in that I want to add US DEM elevation rasters to the database to enable my application to query and receive a graph seeded with these elevation values depending on my users' location. This problem has almost no good documentation, and most solutions are outdated or fail on my test server. Other services that provide elevation on demand are either too costly or do not provide a small enough resolution for my use case. Thus, I hope that anyone can aid me in the setup and deployment on this bespoke database.

[James Keener]

Can you explain the issue you're having and what you've tried? I've loaded the DEM data into postgis before and have been able to query it and use it in QGIS.

Jim

On Sun, Jun 20, 2021 at 3:57 PM Siddarth Madala <sidm...@gmail.com> wrote:

I was looking for some assistance with the following problem. My use case of PostGIS is to store the United States' OpenStreetMap (OSM) data. This is a solved problem with a significant amount of documentation available. However, my use case differs in that I want to add US DEM elevation rasters to the database to enable my application to query and receive a graph seeded with these elevation values depending on my users' location. This problem has almost no good documentation, and most solutions are outdated or fail on my test server. Other services that provide elevation on demand are either too costly or do not provide a small enough resolution for my use case. Thus, I hope that anyone can aid me in the setup and deployment on this bespoke database.

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Siddarth Madala]

I have loaded the OSM data and uploaded the rasters into the database. However I cannot find a way to query all data within a radius of a (long, lat) coordinate pair while seeding each returned part of the data with its corresponding elevation value from the rasters. Any query with ST_DWithin or similar does not return any rows even though the database is populated. It could be a projection issue. If you would be able to point me in the direction of any tutorials/walkthroughs of how to do this, I would appreciate it.

[Regina Obe]

Siddarth,

 

Do you know what spatial reference system your raster data is in?

 

Do this:

 

SELECT ST_AsText(rast::geometry), ST_SRID(rast)

FROM your_raster_table LIMIT 1;

 

You should see coordinates  that resemble your geometry data (and all the coordinates should be less than 180) and srid should be 4326.

 

If you see big numbers, then your raster DEM is not in long lat.  If the srid reads something other than 4326, then your data is not in WGS long lat, but good news is  that is fine.

Figure out what SRID it’s in – might need to read the data source you got it from.

 

If your raster data is not in long lat, transform your geometry data to the same spatial ref sys.

 

SELECT ST_Value(rast,1, p.geom)

FROM your_raster_table AS r  INNER JOIN (SELECT ST_Transform( ST_SetSRID( ST_Point(long,lat), 4326) , <srid of raster>)

 AS geom) AS p ON r.rast && p.geom;