Re: [postgis-users] null to zero

Maybe, you will have to use count(foo.edge_data.geom) instead of count(*) as Tom underlined it before. Hugues Francois Hugues a écrit : Hello, I'm not sure to understand your problem but maybe st_numgeometries is not the right function to use since it counts geometries. Wit

Re: [postgis-users] null to zero

Hello, I'm not sure to understand your problem but maybe st_numgeometries is not the right function to use since it counts geometries. Within a multipolygon. Maybe am i wrong but why don't you use count() like a similar problem suggested it recently ? Select foo1.id, coalesce(count(*), 0) Fro

Re: [postgis-users] Count Points in Polygon with Postgis

) ) as foo Group by poly_id Union Select poly_id, 0 from table_point, table_poly Where not st_contains(poly_geom, point_geom) Hugues De : postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@lists.osgeo.org] De la part de Francois Hugues Envoyé

Re: [postgis-users] Count Points in Polygon with Postgis

Hello, The following query should work well with your data : Select poly_id, count(*) as nb_point from (select poly_id, point_id from table_point, table_poly Where st_contains(point_geom, poly_geom) ) as foo Group by poly_id Hugues.

Re: [postgis-users] don't send me emails

http://lists.osgeo.org/mailman/listinfo/postgis-users From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of manos voyatzakis Sent: Tuesday, February 19, 2013 6:56 AM To: postgis-users@lists.osgeo.org Subject: [po

Re: [postgis-users] Advice on geometry collection to multipolygonapproach

Hi, We do not see your whole query but maybe you should add a where st_intersects clause between the geometries you are intersecting to avoid empty geometry collections. The dump solution will return a record for each component of the geometry collection : simple objects or multi. St_multi(st_dum

Re: [postgis-users] Create constrain between Province and town tables

Hello, I'm not sure to understand what you mean by creating a constraint between two table but I may suggest you to use update statement like this (I assume you want to update the code_prov column thought i don't understand why it is character varying type when your province gid is of serial type

Re: [postgis-users] How create a polygon rectangular from the BBOX of ageometry.

Hi, I think this error comes from a mismatch between your non pojected geometry and column type. You may try : 1. st_SetSRID(st_extent(a.geometry), 3003) 2. you may try another test with something like st_envelope(st_union(st_envelope(a.geometry))) Hugues. -Original Message- From: pos

Re: [postgis-users] Viewing the PostGIS in Action examples in QGIS

Hi, I think you can also use the building query option in the "add postgis layer " window once you're connected to your postgis server and copy /paste one of your query. Hugues James David Smith a écrit : Hi Charles, As Atle says, you won't be able to view that point or the polygon in QGIS

Re: [postgis-users] How to update (replace) a Postgis table?

Hello, I think the reason why your renaming does not work comes from your index. If you want to use the renaming strategy you may have two strategies : - use alter index after altertable to rename - import with shp2pgsql without -I option and create the index on the new populated table with a dif

Re: [postgis-users] Postgresql create function

Hello, Maybe you should try to write the end of your funtion like this : return query your query return; Hugues. From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of tasha dewasi Sent: Monday, February

Re: [postgis-users] postgis raster

Hello, If you want to see your rasters in qgis you should : 1. add raster constraint using -C option if you didn't 2. you may try WKT rasters plugin for qgis 3. you can try to use st_dumpaspolygons For the last two options, this page will provide more informations : http://trac.osgeo.org/post

Re: [postgis-users] (no subject)

Hi, I think this is a tolerance issue. If you try select st_distance(ST_MakeEnvelope(-105.17353271032549,39.798766541338225,-105.07765728967452,39.83016145866177,4326), st_setsrid(ST_MakePOint(-105.130637 ,39.830165),4326)) The distance is very short : 3.54133823066149e-06 And i you add t

Re: [postgis-users] PostGIS Raster and Overviews

Hello, I'm not sure to understand everything about what you are trying to do, but if i'm not wrong, you try to rasterize some geometries. I'm not sure that's the better way to work, but i don't really know how your data looks like. Anyaway, it seems to me you're misunderstanding about rasters

Re: [postgis-users] Why postgis doesn't use index during query?

i do? How do I calculate the X? Thank you. 2013/1/22 Francois Hugues Hello, In the with of your query, you are searching for the min distance and you calculate all distances between every polygons. Try add a where clause st_distance< X to avoid unuseful cal

Re: [postgis-users] Why postgis doesn't use index during query?

Hello, In the with of your query, you are searching for the min distance and you calculate all distances between every polygons. Try add a where clause st_distance< X to avoid unuseful calcul (you should find the max of the min to set X value). This may improve performances. Hugues Carsten

Re: [postgis-users] ST_Buffer returning multipolygons

_dump and filter out any little slivers using st_area() > tolerance HTH, -Steve On 1/18/2013 12:40 PM, marquz74 wrote: > Stephen Woodbridge wrote >> On 1/18/2013 11:10 AM, marquz74 wrote: >>> Francois Hugues wrote >>>> We don't know what are your origina

Re: [postgis-users] ST_Buffer returning multipolygons

Hi, We don't know what are your original geometries or what kind of buffer you apply (since you work with linestrings we can assume it is a positive buffer). Maybe some of your linestrings are multilinestrings with disjoint components. Did you try GeomeryType (geom) group by Geometry types ? What

Re: [postgis-users] ST_Buffer returning multipolygons

Hello, If your input is a multipolygon, st_buffer may return a multipolygon since your buffered the buffered polygons which compose your multigeometry may not intersect each other. Hugues. -Original Message- From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@l

Re: [postgis-users] No intersection between two rasters notice

the issue is due to adjacent tiles. If you're using PostGIS 2.0, add something like... AND ST_Touches(rast1::geometry, rast2::geometry) != TRUE If you're running PostGIS from svn... AND ST_Touches(rast1, rast2) != TRUE -bborie On 01/15/2013 08:45 AM, Francois Hugues wrote: > H

[postgis-users] No intersection between two rasters notice

Hi everyone, I'm facing a new difficulty with rasters. This time with st_MapAlgebraExpr (the whole query is below). I have this notice when I try to cross two rasters with exactly similar tiles. NOTICE: The two rasters provided have no intersection. Returning no band raster To avoid to cross ti

Re: [postgis-users] clip polygons

Hello, To complete these answers about the usage st_intersection in combination with st_intersects, if you want to avoid to clip features "which simply intersect the border" you should try to add st_touches(a.geom, b.geom) = 'f' in the where clause. Hugues -- Hugues FRANÇOIS IR - U

Re: [postgis-users] selecting a time window

Hello, I was answering in a similar way to build lines from your data, but I wondered about the time window strategy and its origin. Is it : 1. a regular time from the beginning to the end of your data as Nicolas solution ? 2. a time window for each message : one second before and one second afte

Re: [postgis-users] St_reclass and no data value

/2013 06:57 AM, Francois Hugues wrote: > Hello, > > I come from an original rast dem where I want to set pixel to 1/0. I > think I have to use st_reclass so I tried this : > > select rid, st_reclass(rast, 1, '[0-800):1, [800-1):0', '8BUI', > -) ra

[postgis-users] St_reclass and no data value

Hello, I come from an original rast dem where I want to set pixel to 1/0. I think I have to use st_reclass so I tried this : select rid, st_reclass(rast, 1, '[0-800):1, [800-1):0', '8BUI', -) rast from rasters.dem_aster_fra where rid =1 And I don't understand why if I'm looking to st

Re: [postgis-users] constraint issue in 2.0.2 r10789

Hello, Sorry for previous message : finger error. To give you some answers to your question : 1. In post gis 2 you may use typemod instead of constraints. This change the behavior of addgeometrycolumn function. You may read the notes here : http://postgis.refractions.net/docs/AddGeometryColumn.

Re: [postgis-users] constraint issue in 2.0.2 r10789

Hello, "Gery ." a écrit : Hello, I recently upgraded both my postgresql (from 8.4.4 to 9.2.2) and postgis (from 1.5.3 to 2.0.2 r10789). With the previous versions I run these commands without problems: [code] CREATE TABLE profiles_line_wgs84( GID SERIAL, ID SMALLINT, FID SMALLINT, SURVEY

Re: [postgis-users] ST_Polygonize thows an error

Hello, This error seems normal to me : in your sub select cntr_value is aliased as the_geom but does not seem to be of geometry type. If it is really a value, double precision should be correct and st_polygonize is waiting for a geometry. Hugues. Message d'origine De: postgis

Re: [postgis-users] How to perform a join on simultaneous spatial and attribute criteria in PostGIS

Hello, To keep the nearest object, i think you should calculate minimal distance first and then use it as a join attribute. Something like this query should work : with dist_min as ( select a.gid, min(st_distance(a.geom, b.geom)) dist_min from table1 a, table2 b where a.country = b.co

[postgis-users] Insert geometry into remote database

Hi all, We need to use our server in production which is postgis 1.5 and we need to automate some operations with another server running postgis 2.0.1 (correction of surface with mnt / slopes) Here is the error : operator is not unique: text || geometry I understand it, but don't see how inser

Re: [postgis-users] Incorrect geometry_columns?

Hi, Are you sure you don't have mixed srid ? You should try this instead of limit 1 : Select st_srid(geom) from merge_strade group by st_srid(the_geom) Hugues. -- Hugues FRANÇOIS Ingénieur recherche Irstea Grenoble 2 rue de la papeterie 38400 Saint-Martin d'Hères tel : +33 (0)4.76.76.

Re: [postgis-users] shp2pgsql -G -r

Hi, For all I know, the -r option is the raw mode used with pgsql2shp. http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide_20.bqg I think you should import geographies to postgis and then use st_transform to get to the wanted projection Hugues. -Message d'origine- De : postgis

Re: [postgis-users] Rast index : Could not get raster's convex hull

ast) As meta FROM rasters.slopes.alpes ) SELECT rid, (rast).width, (rast).height FROM foo WHERE (rast).width < 1 OR (rast).height < 1 -bborie On Thu, Nov 29, 2012 at 12:20 AM, Francois Hugues wrote: > Hello, > > Here it is ! Sorry I have forgotten it the first time. > > &qu

Re: [postgis-users] ST_Intersects Vs. ST_Intersection

Hello, You should directly use st_intersects with st_intersection : SELECT ST_Intersection(table1.geom, table2.geom) >From table1, table2 Where st_intersects(table1.geom, table2.geom) St_intersection is slower than st_intersects. Did you think to build spatial (gist) index on your tables to impr

Re: [postgis-users] ST_Intersection error side location conflict

Hello, Did you try 1. St_isclosed ? 2. To add a where clause st_intersects ? 3. St_makevalid ? 4. The buffer trick (st_buffer(geom, 0)? Hugues. Paolo Crosato a écrit : Hi, I'm trying to get the intersection between a rectangle and a set of multipolygons. They are a lot, SRID is 4326 up to 5

Re: [postgis-users] Rast index : Could not get raster's convex hull

@lists.osgeo.org] De la part de Bborie Park Envoyé : jeudi 29 novembre 2012 02:20 À : postgis-users@lists.osgeo.org Objet : Re: [postgis-users] Rast index : Could not get raster's convex hull Hey Hugues, What is your output for SELECT postgis_full_version() -bborie On 11/26/2012 08:01 A

Re: [postgis-users] Contours of rasters without or with tiles

noble 2 rue de la papeterie 38400 Saint-Martin d'Hères tel : +33 (0)4.76.76.27.44 port : +33 (0)6.77.66.21.31 fax : +33 (0)4.76.51.38.03 -Message d'origine- De : Mathieu Basille [mailto:basi...@ase-research.org] Envoyé : mardi 27 novembre 2012 00:14 À : Francois Hu

Re: [postgis-users] Contours of rasters without or with tiles

Hello, Did you try with the bounding boxes of your tiles using st_envelope ? I operated numerous tiles and unioned them with this function. Your picture does not show us how many lines your last query returns. Here, it could be a multipolygon. Hugues. -Message d'origine- De : postg

[postgis-users] Rast index : Could not get raster's convex hull

Hello, Here is an error I don't understand when I want to index som tiles from a raster table : ERROR: RASTER_convex_hull: Could not get raster's convex hull Here is the query which does not work: create index slopes_alpes_rast on rasters.slopes_alpes using gist(st_convexhull(rast)); Here are

Re: [postgis-users] st_difference throws an error

uld be a bug in st_difference? Thanks, Ángel On 12/11/12 12:42, Francois Hugues wrote: You should try the st_buffer tip : select st_astext( st_difference( st_buffer(st_geomfromtext('POLYGON((-5.6 43.53301,-5.66704 43.533

Re: [postgis-users] st_difference throws an error

You should try the st_buffer tip : select st_astext( st_difference( st_buffer(st_geomfromtext('POLYGON((-5.6 43.53301,-5.66704 43.53335,-5.66931 43.53374,-5.66975299919511 43.533380063154,-5.66979 43.53335,-5.66971 43.53478,-5.66969091189271 43.5348058613067,-5.67026768864541 4

Re: [postgis-users] intersecting and exporting

To export your data to a shapefile, you can use pgsql2shp. Hugues Seb a écrit : On Fri, 9 Nov 2012 15:21:23 +0100, "Francois Hugues" wrote: > The right use of st_intersection imply the use of st_intersects in the > where clause to avoid unuseful empty geometry collection.

Re: [postgis-users] intersecting and exporting

postgis-us...@postgis.refractions.net Objet : Re: [postgis-users] intersecting and exporting On Fri, 9 Nov 2012 08:34:07 +0100, "Francois Hugues" wrote: > Hello, To plot this in a gis you don't really need to export it you > can add a query layer in most of the gis software : - openjump : >

Re: [postgis-users] intersecting and exporting

Hello, To plot this in a gis you don't really need to export it you can add a query layer in most of the gis software : - openjump : layer/sql query - qgis : layer/new postgis layer - arcgis : file/add data/add query layer You can directly use your intersection query or select * from subset1 Hu

Re: [postgis-users] clip raster image

Hello, Never tried it, but I think st_dumpaspolygon should help you to make the polygon to clip your raster with : http://postgis.refractions.net/docs/RT_ST_DumpAsPolygons.html Hugues. De : po

Re: [postgis-users] FYI: Solution to Using PostGIS in ArcGIS.

, libpq.dll, and ssleay32.dll pour votre windows 32 bits ·libeay32.dll, libintl.dll, libpq.dll, and ssleay32.dll pour votre windows 64 bits Hugues. Message d'origine De: postgis-users-boun...@lists.osgeo.org de la part de Francois Hugues Date: mer. 07/11/2012 10:21 À: Po

Re: [postgis-users] FYI: Solution to Using PostGIS in ArcGIS.

Some tips to connect to postgis from arcgis 10 with windows : Pour une connexion à une base de données PostGIS, il faut copier les librairies dll de votre client de connexion pgadmin dans le répertoire bin de votre installation arcgis (ex C:\Program Files\ArcGIS\Desktop10.1\bin. ·libeay32.dll,

Re: [postgis-users] the_geom is empty

Sometimes, the geometry column does not show anything but that does not necessarily means it is empty. Did you try a simple request like select st_isclosed(the_geom) from yourtable or try to load your geometries with openjump. Hugues. Message d'origine De: postgis-users-boun.

Re: [postgis-users] How to re-tile a raster after union ?

borie On 10/31/2012 12:59 PM, Francois Hugues wrote: > Hi, > > I have a tiled dem which I want to use to calculate slopes. If I want to > avoid the border effect between each tile, I have to use st_union (the faster > version, thanks to P. Racine). > > But, if I'm not

[postgis-users] How to re-tile a raster after union ?

Hi, I have a tiled dem which I want to use to calculate slopes. If I want to avoid the border effect between each tile, I have to use st_union (the faster version, thanks to P. Racine). But, if I'm not wrong, I will obtain a single tile raster. The answer to my question is maybe so obvious tha