[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] OSM and US DEM in PostGIS
From: "Regina Obe" <lr () pcorp ! us>
Date: 2021-06-27 17:57:05
Message-ID: 001f01d76b7d$d7acf780$8706e680$ () pcorp ! us
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/alternative)]
This is a multipart message in MIME format.
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;
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of \
Siddarth Madala
Sent: Thursday, June 24, 2021 12:33 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] OSM and US DEM in PostGIS
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.
On Sun, Jun 20, 2021 at 9:45 PM James Keener <jim@jimkeener.com \
<mailto:jim@jimkeener.com> > wrote:
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 <sidmadala@gmail.com \
<mailto:sidmadala@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
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 \
(filtered medium)"><style><!-- /* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div \
class=WordSection1><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Siddarth,<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Do you know \
what spatial reference system your raster data is in?<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Do \
this:<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT \
ST_AsText(rast::geometry), ST_SRID(rast)<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM \
your_raster_table LIMIT 1;<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>You should \
see coordinates that resemble your geometry data (and all the coordinates should be \
less than 180) and srid should be 4326.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Figure out \
what SRID it's in – might need to read the data source you got it \
from.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If your \
raster data is not in long lat, transform your geometry data to the same spatial ref \
sys.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT \
ST_Value(rast,1, p.geom)<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>FROM \
your_raster_table AS r INNER JOIN (SELECT ST_Transform( ST_SetSRID( \
ST_Point(long,lat), 4326) , <srid of raster>) <o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> AS geom) AS \
p ON r.rast && p.geom;<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div \
style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal><b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users \
[mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Siddarth \
Madala<br><b>Sent:</b> Thursday, June 24, 2021 12:33 PM<br><b>To:</b> PostGIS Users \
Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: \
[postgis-users] OSM and US DEM in PostGIS<o:p></o:p></span></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>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.<o:p></o:p></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Sun, Jun 20, \
2021 at 9:45 PM James Keener <<a href="mailto:jim@jimkeener.com" \
target="_blank">jim@jimkeener.com</a>> wrote:<o:p></o:p></p></div><blockquote \
style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal>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.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>Jim<o:p></o:p></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Sun, Jun 20, \
2021 at 3:57 PM Siddarth Madala <<a href="mailto:sidmadala@gmail.com" \
target="_blank">sidmadala@gmail.com</a>> wrote:<o:p></o:p></p></div><blockquote \
style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-right:0in'><div><p class=MsoNormal>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.<o:p></o:p></p></div><p \
class=MsoNormal>_______________________________________________<br>postgis-users \
mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br><a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></blockquote></div><p \
class=MsoNormal>_______________________________________________<br>postgis-users \
mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br><a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></blockquote></div></div></div></body></html>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic