Re: [postgis-users] [Raster] Finding pixels intersecting an extent (polygon)
ST_Intersection(rast, geom) does vectorize the raster before intersecting with the polygon. ST_Clip(rast, geom) rasterizes the polygon before intersecting with the raster. Can you provide more information? Such as the query and the version of PostGIS? -bborie On Thu, Feb 5, 2015 at 2:05 PM, Jean Marchal jean.d.marc...@gmail.com wrote: Hi list, I am trying to return all the pixels in a raster that intersect (not just touch) an extent (say a rectangle). I tried ST_Clip and ST_Intersection(raster, geom) but they don't return all the pixels that intersect my extent polygon. Do I have to vectorize the raster first using ST_PixelAsPolygons or there is a better / more efficient way to proceed? Ultimately the goal is to fetch the resulting raster in R. Thanks, Jean ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Convert from Lat/Long point to postGIS geometry
Hi, try this http://www.postgis.org/docs/ST_GeomFromText.html Marco Am 03.02.2015 um 05:11 schrieb KhunSanAung: Hi All, I have one table (Town info) in postgres without Geometry field. I have Latitude and Longitude information for those points data separately (collecting filling). I created the postGIS extension and add the Geometry field in the above postgres table. Now, I'd like to add the location information into the postGIS geometry field so that I can immediately view those points on the map. How can I convert the Latitude/Longitude value into postGIS geometry value? Thank you very much in advance. -- Have a nice day! -- *Mr. Khun San Aung* ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users 0x6A30F373.asc Description: application/pgp-keys ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Creating a DBlink to load data from Oracle Spatial to PostGIS
Hello, Le jeudi 29 janvier 2015, 15:22:15 Tahir Tamba a écrit : [...] Otherwise, are there another ways to load tables from Oracle Spatial database to PostGIS ? As Brent suggested, you can use Oracle Foreign Data Wrapper. There is now full support for Oracle spatial included in Oracle_fdw natively and without dependency. Once setup, you can access your Oracle tables from within PostgreSQL/PostGIS transparently, and can use materialized views on foreign table if you want a cache mechanism to improve performances. This is AFAIK the most performant way to do, and totally transparent from an application point of view. https://github.com/laurenz/oracle_fdw Note that ora2pg (since version 14) can generate directly the oracle_fdw foreign tables, or completely migrate your data : http://ora2pg.darold.net/news.html Thanks to Laurenz, Gilles and Vincent for this work. Vincent -- Vincent Picavet - Gérant Oslandia www.oslandia.com ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] [Raster] Finding pixels intersecting an extent (polygon)
Hi list, I am trying to return all the pixels in a raster that intersect (not just touch) an extent (say a rectangle). I tried ST_Clip and ST_Intersection(raster, geom) but they don't return all the pixels that intersect my extent polygon. Do I have to vectorize the raster first using ST_PixelAsPolygons or there is a better / more efficient way to proceed? Ultimately the goal is to fetch the resulting raster in R. Thanks, Jean ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] [Raster] Finding pixels intersecting an extent (polygon)
select ST_Clip(r.rast,p.geom) as rast from polygon p inner join raster r on ST_intersects(r.rast, p.geom) This returns a raster which has all pixels inside the polygon On Thu, Feb 5, 2015 at 4:05 PM, Jean Marchal jean.d.marc...@gmail.com wrote: Hi list, I am trying to return all the pixels in a raster that intersect (not just touch) an extent (say a rectangle). I tried ST_Clip and ST_Intersection(raster, geom) but they don't return all the pixels that intersect my extent polygon. Do I have to vectorize the raster first using ST_PixelAsPolygons or there is a better / more efficient way to proceed? Ultimately the goal is to fetch the resulting raster in R. Thanks, Jean ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] [Raster] Finding pixels intersecting an extent (polygon)
Try something like:
WITH foo AS (
SELECT
ST_AsRaster(
ST_GeomFromText('POLYGON ((-52.54178994517749 46.99199259385565,
-52.54178994517749 46.996897959881, -52.53436080387823 46.996897959881,
-52.53436080387823 46.99199259385565, -52.54178994517749
46.99199259385565))', 4269),
rast,
'8BUI',
touched := True
) AS rast
FROM elev
LIMIT 1
)
SELECT
ST_Intersection(
elev.rast,
foo.rast
) AS rast
FROM elev
JOIN foo
ON ST_Intersects(elev.rast, foo.rast)
The idea is to explicitly convert the geometry into a raster using one of
the elev rasters as a reference. The touched := True slightly changes the
behavior of the rasterization.
From the docs:
The optional touched parameter defaults to false and maps to the GDAL
ALL_TOUCHED rasterization option, which determines if pixels touched by
lines or polygons will be burned.
On Thu, Feb 5, 2015 at 2:45 PM, Jean Marchal jean.d.marc...@gmail.com
wrote:
Bborie,
I am running PostGIS 2.1.5. Here is the output of the
postgis_full_version():
POSTGIS=2.1.5 r13152 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23
September 2009 GDAL=GDAL 1.9.0, released 2011/12/29 LIBXML=2.8.0
LIBJSON=UNKNOWN RASTER
My query looks like this:
SELECT ST_Intersection(ST_GeomFromText('POLYGON ((-52.54178994517749
46.99199259385565, -52.54178994517749 46.996897959881, -52.53436080387823
46.996897959881, -52.53436080387823 46.99199259385565, -52.54178994517749
46.99199259385565))', 4269), rast) as rast
FROM elev
David,
This query does not returns all the polygons that intersect my polygon. I
think it returns only those where the centroid is inside the polygon or is
it covered based? (like 50% of the pixel intersect with the polygon).
Thanks for your rapid answers!
Jean
2015-02-05 14:31 GMT-08:00 David Haynes hayne...@gmail.com:
select ST_Clip(r.rast,p.geom) as rast
from polygon p inner join raster r on ST_intersects(r.rast, p.geom)
This returns a raster which has all pixels inside the polygon
On Thu, Feb 5, 2015 at 4:05 PM, Jean Marchal jean.d.marc...@gmail.com
wrote:
Hi list,
I am trying to return all the pixels in a raster that intersect (not
just touch) an extent (say a rectangle). I tried ST_Clip and
ST_Intersection(raster, geom) but they don't return all the pixels that
intersect my extent polygon. Do I have to vectorize the raster first using
ST_PixelAsPolygons or there is a better / more efficient way to proceed?
Ultimately the goal is to fetch the resulting raster in R.
Thanks,
Jean
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] [Raster] Finding pixels intersecting an extent (polygon)
It worked! Thanks a lot!
Jean
2015-02-05 15:18 GMT-08:00 Bborie Park dustym...@gmail.com:
Try something like:
WITH foo AS (
SELECT
ST_AsRaster(
ST_GeomFromText('POLYGON ((-52.54178994517749 46.99199259385565,
-52.54178994517749 46.996897959881, -52.53436080387823 46.996897959881,
-52.53436080387823 46.99199259385565, -52.54178994517749
46.99199259385565))', 4269),
rast,
'8BUI',
touched := True
) AS rast
FROM elev
LIMIT 1
)
SELECT
ST_Intersection(
elev.rast,
foo.rast
) AS rast
FROM elev
JOIN foo
ON ST_Intersects(elev.rast, foo.rast)
The idea is to explicitly convert the geometry into a raster using one of
the elev rasters as a reference. The touched := True slightly changes the
behavior of the rasterization.
From the docs:
The optional touched parameter defaults to false and maps to the GDAL
ALL_TOUCHED rasterization option, which determines if pixels touched by
lines or polygons will be burned.
On Thu, Feb 5, 2015 at 2:45 PM, Jean Marchal jean.d.marc...@gmail.com
wrote:
Bborie,
I am running PostGIS 2.1.5. Here is the output of the
postgis_full_version():
POSTGIS=2.1.5 r13152 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23
September 2009 GDAL=GDAL 1.9.0, released 2011/12/29 LIBXML=2.8.0
LIBJSON=UNKNOWN RASTER
My query looks like this:
SELECT ST_Intersection(ST_GeomFromText('POLYGON ((-52.54178994517749
46.99199259385565, -52.54178994517749 46.996897959881, -52.53436080387823
46.996897959881, -52.53436080387823 46.99199259385565, -52.54178994517749
46.99199259385565))', 4269), rast) as rast
FROM elev
David,
This query does not returns all the polygons that intersect my polygon. I
think it returns only those where the centroid is inside the polygon or is
it covered based? (like 50% of the pixel intersect with the polygon).
Thanks for your rapid answers!
Jean
2015-02-05 14:31 GMT-08:00 David Haynes hayne...@gmail.com:
select ST_Clip(r.rast,p.geom) as rast
from polygon p inner join raster r on ST_intersects(r.rast, p.geom)
This returns a raster which has all pixels inside the polygon
On Thu, Feb 5, 2015 at 4:05 PM, Jean Marchal jean.d.marc...@gmail.com
wrote:
Hi list,
I am trying to return all the pixels in a raster that intersect (not
just touch) an extent (say a rectangle). I tried ST_Clip and
ST_Intersection(raster, geom) but they don't return all the pixels that
intersect my extent polygon. Do I have to vectorize the raster first using
ST_PixelAsPolygons or there is a better / more efficient way to proceed?
Ultimately the goal is to fetch the resulting raster in R.
Thanks,
Jean
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Find out if polygon is circle
Thanks to all of you very much for your insightful replies! The circles are computer-generated with 15-20 points (I should've mentioned that from the beginning). I very much like the compactness-approach as well as the LineToCurve. I will try out these approaches and let you know about my solution. Again thank you very much, this mailing list is impressive and I hope I will be able to contribute as well. Very best from Berlin, Wolfgang On 5 February 2015 at 19:56, Brent Wood pcr...@pcreso.com wrote: I have many different polygons in my database and would like to know if there's any way to find out if a polygon has the shape of a circle. I searched both on Google and in the Postgis documentation but couldn't find someone with the same question. The lines joining the vertices of a polygon are straight - so implicitly any polygon defined by a sequence of points is NOT a circle, but might approximate one. Also, a reprojected circle may no longer be round. Any circle can be defined by three points, but a triangle is not a circle :-) If what you are asking is whether each vertex in the polygon boundary is the same distance from the centroid - that might be done relatively easily. Something along the lines of (off the top of my head - this will need work to work): select count(distinct(ST_Distance(ST_Centroid(geom),((ST_DumpPoints(ST_exteriorRing(geom))).geom for each polygon get the vertices, then get the distance between each vertex the centroid, then see how many distinct distances there are for each feature, if only one you have a circle - the distance being the radius. If precision becomes a problem with near zero differences creating apparently different radii for the polygon, you can select where max(dist) - min(dist) a_very_small_number instead of all being the same. Cheers Brent Wood ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Find out if polygon is circle
Hey,
your question is a little bit un-precise.
If you try to find circle that have been drawn by a CAD program for
instance (perfect circle),
you can use ST_LineToCurve().
If you are trying to detect polygons that look like circle (maybe the
border is very close to a circle, but overall it is really close to a
circle),
you can use a simplified Hough Transform.
(for each 3 successive points in your exterior ring, compute the associated
circle (centre + radius), then merge the results with a tolerance, then
decide if it is a circle or not).
(lastly, if you polygon may be so noisy that you want in fact to fit a
circle, which is another story)
I wrote an equivalent to ST_LineToCurve that has a precision parameter,
thus that will be robust if your circle are not perfect.
It is here
https://github.com/Remi-C/_utilities/blob/aa802f136cc3768d987b77cb3a4d04e9f6a74344/postgis/arcs/rc_circularHoughTransform.sql
In both case, what you get after calling ST_LineToCurve or rc_lineToCurve
is a geometry with potentially a mix of line and curve.
You must then define how to decide based on this results if you accept the
polygon as a circle or not (or maybe return a circleness indice )
Cheers,
Rémi-C
2015-02-05 5:01 GMT+01:00 Paragon Corporation l...@pcorp.us:
I have many different polygons in my database and would like to know if
there's any way to find out if a polygon has the shape of a circle. I
searched both on Google and in the Postgis documentation but couldn't find
someone with the same question.
One thought – use the ST_LineToCurve function and see if it comes back
with a circularstring or one of the curved family of geometry types
A circle would be a circularstring with 3 points
http://postgis.net/docs/manual-2.1/ST_LineToCurve.html
e.g.
SELECT ST_AsText(ST_LineToCurve(ST_Boundary(ST_Buffer(ST_Point(1,2),10;
--
CIRCULARSTRING(11 2,-9 1.97,11 2)
SELECT
ST_GeometryType(ST_LineToCurve(ST_Boundary(ST_Buffer(ST_Point(1,2),10;
ST_CircularString
If your geometry has any kind of curve, it should come back different
So for example
SELECT ST_GeometryType(ST_LineToCurve(ST_GeomFromText('LINESTRING(1 2, 3
4, 5 6, 7 8)')))
ST_LineString
Hope that helps,
Regina
http://www.postgis.us
http://postgis.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] External rasters: access disabled?
Hi Paolo, I bumped into this as well, but it is by design and the solution is fairly easy. Read this: http://postgis.net/docs/postgis_installation.html#install_short_version Bottom line: ``` As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. If you want to enable offline raster: POSTGIS_ENABLE_OUTDB_RASTERS=1 Any other setting or no setting at all will disable out of db rasters. ``` Chrs, Tom On 5-2-2015 11:07, Paolo Cavallini wrote: Hi all. Apparently external (-R) raster are no longer accessible: SELECT ST_value(rast,ST_SetSRID (ST_Point(1598700,4884700), 3003)) FROM dtm_ext; ERROR: rt_raster_load_offline_data: Access to offline bands disabled CONTEXT: funzione PL/pgSQL st_value(raster,integer,geometry,boolean) riga 18 a RETURN Debian sid, pg 9.4, PostGIS 2.1.4+dfsg-2, GDAL 1.10.1+dfsg-8+b3 They worked before. As it stands, they are pretty useless. Anyone has a good explanation for this? All the best, and thanks. ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Find out if polygon is circle
I have many different polygons in my database and would like to know if there's any way to find out if a polygon has the shape of a circle. I searched both on Google and in the Postgis documentation but couldn't find someone with the same question. The lines joining the vertices of a polygon are straight - so implicitly any polygon defined by a sequence of points is NOT a circle, but might approximate one. Also, a reprojected circle may no longer be round. Any circle can be defined by three points, but a triangle is not a circle :-) If what you are asking is whether each vertex in the polygon boundary is the same distance from the centroid - that might be done relatively easily. Something along the lines of (off the top of my head - this will need work to work):select count(distinct(ST_Distance(ST_Centroid(geom),((ST_DumpPoints(ST_exteriorRing(geom))).geom for each polygon get the vertices, then get the distance between each vertex the centroid, then see how many distinct distances there are for each feature, if only one you have a circle - the distance being the radius. If precision becomes a problem with near zero differences creating apparently different radii for the polygon, you can select where max(dist) - min(dist) a_very_small_number instead of all being the same. Cheers Brent Wood ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] External rasters: access disabled?
Il 05/02/2015 10:24, Tom van Tilburg ha scritto: Hi Paolo, I bumped into this as well, but it is by design and the solution is fairly easy. Read this: http://postgis.net/docs/postgis_installation.html#install_short_version OK, clear, thanks. -- Paolo Cavallini - www.faunalia.eu QGIS PostGIS courses: http://www.faunalia.eu/training.html New course: QGIS for naturalists - http://www.faunalia.eu/en/nat_course.html ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
