Paul Ramsey | 1 Jul 16:47 2012

Re: cross-database queries and choice of database configuration

Because the feature he wants, separately backing up spatial and
attributes, can be easily achieved inside one database by putting the
different parts in different schemas. At the same time, it would be
common to expect to run queries that would going the spatial to the
attributes, and that would be very inefficient indeed over a
cross-database dblink or fdw connection.

Just do not store data you want to join together in different
databases, full stop.

P.

On Fri, Jun 29, 2012 at 9:10 PM, shirabez <shira <at> sfei.org> wrote:
> Following up on this thread from a few months ago, can you give a rationale
> for this perspective? Why is it better to go with one database? Performance?
> Simplicity? Both? Thanks,
>
> Shira
>
>
>
> Paul Ramsey-4 wrote
>>
>> On Thu, Mar 22, 2012 at 4:53 AM, Tim Pigden &lt;tim.pigden <at> &gt; wrote:
>>> Is this a practical approach for postgis type queries or do I have to put
>>> it
>>> all in one database to get good querying flexibility?
>>
>> No, bad approach. Put it all in one database, with the application
>> data in one schema and the geo data in another. Then for regular
(Continue reading)

DavidRA | 1 Jul 19:44 2012
Picon

Re: Extracting cell values into a matrix

I need to do an ordered weighted averaging with the input rasters: assuming
they all have the same extension and characteristics (like pixel size), for
position (1,1) I take the value of every raster, the I sort those values to
assign the weights and finally I compute the average to store it in the
(1,1) position of the output raster. And so on with the rest of the
positions.

As you can see, I need to extract every pixel of every raster.

--
View this message in context: http://postgis.17.n6.nabble.com/Extracting-cell-values-into-a-matrix-tp4998636p4998700.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
Olivier Courtin | 2 Jul 10:46 2012

Re: ArcGIS and PostGIS feedback


On Jun 30, 2012, at 8:44 AM, Simon Greener wrote:

Simon,

So, while this comment was about a different database/data type to PostgreSQL/PostGIS, I think the same issues would apply.

Tks for your feedback !

--
Olivier



_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Simon Greener | 2 Jul 12:40 2012

Re: ArcGIS and PostGIS feedback

Olivier,

I note that some of the FDO drivers support GeoDatabase versions. This is of course fine if you are prepared to use the ArcSDE dlls which normally underly the drivers.

If you want data-level (at the database) integration, the knowns are known, as are the unknowns but will take effort to turn the unknowns into knowns. Not hard, just effort. (I note that it has taken nearly 15 years to finally crack the .sbn/.sbx for the shapefile.... let's hope it is not so long for versions and deltas.)

regards
Simon
On Mon, 02 Jul 2012 18:46:29 +1000, Olivier Courtin <olivier.courtin <at> oslandia.com> wrote:


On Jun 30, 2012, at 8:44 AM, Simon Greener wrote:

Simon,

So, while this comment was about a different database/data type to PostgreSQL/PostGIS, I think the same issues would apply.

Tks for your feedback !

--
Olivier






--
Holder of "2011 Oracle Spatial Excellence Award for Education and Research."
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: simon <at> spatialdbadvisor.com
Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
René Fournier | 2 Jul 13:10 2012

Super weird problem: ST_GeomFromText('POINT(-114.112534 50.895364)') works, ST_GeomFromText('POINT(-114.228869 51.152249)') fails -- why?

If I try to insert a row containing particular coordinate, the query fails and the DB connection is lost. (By comparison, hundreds of inserts of other coordinates work fine.) Here's a straight copy-and-paste comparison from psql:

mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id;
 id  
-----
 333
(1 row)

INSERT 0 1
mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id;
The connection to the server was lost. Attempting reset: Failed.
!> 

Here's the table definition:

CREATE TABLE public.addresses
(id serial NOT NULL,
account_id int NOT NULL,
territory_id int NOT NULL,
location GEOGRAPHY(POINT,4326),
PRIMARY KEY (id));
CREATE INDEX location ON addresses USING GIST (location);

Strange right? FWIW, the queries are being generated programmatically by a script, so the error is not caused by a typo, since hundreds of other inserts work. Also, I've done a little research, two interesting findings:

1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the SUCCESSFUL inserts are LOWER than the coordinates of failed query.

2. If I create the table without the index on location, the failed inserts suddenly work. So it seems the problem lies with the PostGIS updating the Index -- maybe it doesn't like the size of the values of the larger coordinates?

Anyway, if you have any ideas of what I can do to fix this, I would love to hear them. Thanks!

...Rene
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Sandro Santilli | 2 Jul 13:22 2012
Picon

Re: Super weird problem: ST_GeomFromText('POINT(-114.112534 50.895364)') works, ST_GeomFromText('POINT(-114.228869 51.152249)') fails -- why?

Please file a ticket, and report the output of postgis_full_version()

--strk;

On Mon, Jul 02, 2012 at 01:10:55PM +0200, René Fournier wrote:
> If I try to insert a row containing particular coordinate, the query fails and the DB connection is lost.
(By comparison, hundreds of inserts of other coordinates work fine.) Here's a straight copy-and-paste
comparison from psql:
> 
> mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0,
ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id;
>  id  
> -----
>  333
> (1 row)
> 
> INSERT 0 1
> mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0,
ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id;
> The connection to the server was lost. Attempting reset: Failed.
> !> 
> 
> Here's the table definition:
> 
> CREATE TABLE public.addresses
> (id serial NOT NULL,
> account_id int NOT NULL,
> territory_id int NOT NULL,
> location GEOGRAPHY(POINT,4326),
> PRIMARY KEY (id));
> CREATE INDEX location ON addresses USING GIST (location);
> 
> Strange right? FWIW, the queries are being generated programmatically by a script, so the error is not
caused by a typo, since hundreds of other inserts work. Also, I've done a little research, two interesting findings:
> 
> 1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the SUCCESSFUL inserts are LOWER than
the coordinates of failed query.
> 
> 2. If I create the table without the index on location, the failed inserts suddenly work. So it seems the
problem lies with the PostGIS updating the Index -- maybe it doesn't like the size of the values of the
larger coordinates?
> 
> Anyway, if you have any ideas of what I can do to fix this, I would love to hear them. Thanks!
> 
> ...Rene
William Humphrey Temperley | 2 Jul 13:41 2012
Picon

Re: Problem with using query layer in ArcGIS 10

 

From: postgis-users-bounces <at> postgis.refractions.net [mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of Melpati, Muni
Sent: 28 June 2012 21:19
To: postgis-users <at> postgis.refractions.net
Subject: [postgis-users] Problem with using query layer in ArcGIS 10

 

Hi I was querying posgis layers from ArcGIS 10. I found that if add postgis layer with huge number of records, I am having trouble with accessing its attributes. When I try to open the table I get “Could not load data from the data source. If you can correct the problem, press the refresh button to reload data. Possible problems can include bad network connection, invalid field, etc. The SQL statement was not a select statement. The operation is not supported by this implementation.”

 

I found similar problem somewhere in GIS forums where the user had to change the MAXBLOBSIZE value to -1 (unlimited) in the SDE SERVER CONFIG FILE. Is there similar fix to postgis/postgres? I appreciate your support.

Thanks.

 

 

 

By default ArcGIS uses all the fields in the query as the unique key.  Tools such as identify labeling and the attribute table do not work when this is the case. To make them work the primary key of the data source should be used as the unique key. This also speeds up the loading of data.

 

After writing a query, you need to click the Validate button and tick “Show advanced options”. The Next button will now be highlighted - click it.  Uncheck all fields in the Unique identifier fields list except the correct primary key or unique field.  The attribute table should now work if you provided a unique key.

 

HTH,

 

Will Temperley

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Gis Mage | 2 Jul 15:28 2012
Picon

raster2pgsql import result looks corrupted when using -t parameter

Hi list,

I'm importing a Float32 type GeoTiff to postgis.

When I'm importing a raster to just one record (without -t parameter),
then resulting raster looks ok in qgis.
https://dl.dropbox.com/u/7488735/untiled.png

When I use a -t 10x10 parameter to load the raster as tiles, the sql
is generated without any errors, psql loads it without any errors
aswell, I get the expected number of rows in the table, but when I
load it in qgis, it looks corrupted - I see lots of corrupted tiles in
the picture, like this:
https://dl.dropbox.com/u/7488735/tiled.png

I get a lot of nodata pixels, where there is data in the initial raster.

I'm using postgresql 9.1.4 + postgis 2.0.1

Is it a problem with float32 rasters?
Does raster2pgplsql use gdal_retile.py script?
Could it be a gdal problem, or it is a problem during loading?
I tried both with -Y parameter and without it, and result looks ugly
in both cases.
Owais Khan | 2 Jul 16:49 2012
Picon

Compilation Issue of Postgis-2.0.0 on MinGW

Hello All,


I was reading following thread but it ends un completed.


I will be thankful if someone can point towards resolution of this.

Thanks & Regards,
Owais.
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Bborie Park | 2 Jul 17:27 2012
Picon

Re: raster2pgsql import result looks corrupted when using -t parameter

On Mon, Jul 2, 2012 at 6:28 AM, Gis Mage <gismage <at> gmail.com> wrote:
> Hi list,
>
> I'm importing a Float32 type GeoTiff to postgis.
>
> When I'm importing a raster to just one record (without -t parameter),
> then resulting raster looks ok in qgis.
> https://dl.dropbox.com/u/7488735/untiled.png
>
> When I use a -t 10x10 parameter to load the raster as tiles, the sql
> is generated without any errors, psql loads it without any errors
> aswell, I get the expected number of rows in the table, but when I
> load it in qgis, it looks corrupted - I see lots of corrupted tiles in
> the picture, like this:
> https://dl.dropbox.com/u/7488735/tiled.png
>
> I get a lot of nodata pixels, where there is data in the initial raster.
>
> I'm using postgresql 9.1.4 + postgis 2.0.1
>
> Is it a problem with float32 rasters?
> Does raster2pgplsql use gdal_retile.py script?
> Could it be a gdal problem, or it is a problem during loading?
> I tried both with -Y parameter and without it, and result looks ugly
> in both cases.

Hi,

It sounds like you are experiencing an issue similar to that described
in ticket #1808.

http://trac.osgeo.org/postgis/ticket/1808

I've been unable to reproduce the problem on my systems using test
rasters matching the attributes of the raster causing the issue.  Are
you able to share your source raster?

-bborie

--

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark <at> ucdavis.edu

Gmane