Re: [postgis-users] how to select points outside polygons?

[Vishal Mehta]

Superb, Steve,

The second option with union worked -( the first did'nt because the geometry 
type of pca_huc was multipolygon.)

Thanks much!
Vishal

-Original Message-
From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen 
Woodbridge
Sent: Thursday, September 29, 2011 4:21 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] how to select points outside polygons?

On 9/29/2011 5:55 PM, Vishal Mehta wrote:
> *Hi Steve, *
>
> **
>
> I tried that
>
> select powerplants.gid, plantname
>
>   from powerplants, pca_huc
>
> where state ='CA'
>
> AND NOT st_contains(pca_huc.geom,powerplants.geom)
>
> order by powerplants.gid;
>
> but got some 44,000 rows in the result. There are 1477 powerplants
> (points) and 299 pca_huc polygons. The powerplants don't all fall 
> inside the polygons, and I want to get the list of those that fall outside.
>
> In a desktop gis I can quickly select and see that 102 powerplants 
> fall
> outside: so I should not be getting 44, rows in the result...

Ahhh! that is a different problem! you want the points the do not fall in ANY 
polygon, so you need to first union or collect all you polygons into a single 
object and then ask which point are out that object.

So maybe this will work:

select powerplants.gid, plantname
   from powerplants,
(select st_collect(geom) as geom from pca_huc) as pca
  where state ='CA'
AND NOT st_contains(pca.geom,powerplants.geom)
  order by powerplants.gid;

If that does not work then try:

select powerplants.gid, plantname
   from powerplants,
(select st_union(geom) as geom from pca_huc) as pca
  where state ='CA'
AND NOT st_contains(pca.geom,powerplants.geom)
  order by powerplants.gid;

-Steve

> V
>
> **
>
> **
>
> Did you try:
>
>
>
> select powerplants.gid, plantname
>
>   from powerplants, pca_huc
>
> where state ='CA'
>
> AND NOT st_contains(pca_huc.geom,powerplants.geom)
>
> order by powerplants.gid;
>
>
>
> This should give you all power plants in CA and not in your contains
>
> clause. What is the coverage of pca_huc table? If this covers all of 
> CA
>
> then there is not possible result. The would be like say where a=1 and
>
> not a=1.
>
>
>
> -Steve W
>
>
>
> On 9/29/2011 1:21 PM, Vishal Mehta wrote:
>
>>/  I have a point layer and a polygon layer. How do I select points 
>>that/
>
>>/  are NOT contained within polygons?/
>
>>/  /
>
>>/  For example, this query correctly selects points contained by 
>>polygons/
>
>>/  /
>
>>/  --/
>
>>/  /
>
>>/  select powerplants.gid, plantname/
>
>>/  /
>
>>/  from powerplants, pca_huc/
>
>>/  /
>
>>/  where state ='CA'   /
>
>>/  /
>
>>/  AND/
>
>>/  /
>
>>/  st_contains(pca_huc.geom,powerplants.geom)/
>
>>/  /
>
>>/  order by powerplants.gid;/
>
>>/  /
>
>>/  --/
>
>>/  /
>
>>/  I tried to get the inverse selection using st_disjoint instead of/
>
>>/  st_contains , but that did not work. I also tried using NOT and 
>>EXCEPT/
>
>>/  without success (although I may not have constructed the latter 
>>queries/
>
>>/  correctly..)/
>
>>/  /
>
>>/  Thanks!/
>
>>/  /
>
>>/  Vishal/
>
> /Vishal K. Mehta <http://sei-us.org/about/staff_person/19>, Ph.D. /
>
> /Staff Scientist, /
>
> /Stockholm Environment Institute-US /
>
> /400 F St, Davis, CA 95616 /
>
> /http://sei-us.org/ /
>
> //
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] how to select points outside polygons?

[Vishal Mehta]

Hi Steve,

I tried that


select powerplants.gid, plantname

   from powerplants, pca_huc

  where state = 'CA'

AND NOT st_contains(pca_huc.geom,powerplants.geom)

  order by powerplants.gid;

but got some 44,000 rows in the result. There are 1477 powerplants (points) and 
299 pca_huc polygons. The powerplants don't all fall inside the polygons, and I 
want to get the list of those that fall outside.

In a desktop gis I can quickly select and see that 102 powerplants fall 
outside: so I should not be getting 44, rows in the result...

V






Did you try:



select powerplants.gid, plantname

   from powerplants, pca_huc

  where state = 'CA'

AND NOT st_contains(pca_huc.geom,powerplants.geom)

  order by powerplants.gid;



This should give you all power plants in CA and not in your contains

clause. What is the coverage of pca_huc table? If this covers all of CA

then there is not possible result. The would be like say where a=1 and

not a=1.



-Steve W



On 9/29/2011 1:21 PM, Vishal Mehta wrote:

> I have a point layer and a polygon layer. How do I select points that

> are NOT contained within polygons?

>

> For example, this query correctly selects points contained by polygons

>

> --

>

> select powerplants.gid, plantname

>

> from powerplants, pca_huc

>

> where state = 'CA'

>

> AND

>

> st_contains(pca_huc.geom,powerplants.geom)

>

> order by powerplants.gid;

>

> --

>

> I tried to get the inverse selection using st_disjoint instead of

> st_contains , but that did not work. I also tried using NOT and EXCEPT

> without success (although I may not have constructed the latter queries

> correctly..)

>

> Thanks!

>

> Vishal


Vishal K. Mehta<http://sei-us.org/about/staff_person/19>, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616
http://sei-us.org/



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] how to select points outside polygons?

[Vishal Mehta]

I have a point layer and a polygon layer. How do I select points that are NOT 
contained within polygons?

For example, this query correctly selects points contained by polygons
--
select powerplants.gid, plantname
from powerplants, pca_huc
where state = 'CA'
AND
st_contains(pca_huc.geom,powerplants.geom)
order by powerplants.gid;
--

I tried to get the inverse selection using st_disjoint instead of st_contains , 
but that did not work. I also tried using NOT and EXCEPT without success 
(although I may not have constructed the latter queries correctly..)

Thanks!
Vishal




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] installing 2 versions of postgis

[Vishal Mehta]

Thanks Regina,
I am about to try it.

Is it possible to have,using this procedure, under the same database, two 
templates (template_postgis20 and template_postgis15), and then create a new 
database with template_postgis15 as the template;

Or is it best to create a new database directly with 1.5 version?

As I understand it, I am going to

-  backup the newer versions of proj.dll and geos*.dll, run the bat 
script and then copy back the newer geos*.dll and proj.dll, and delete the old 
geos*.dll that the 1.5 bat script would copy over

-  I was going to comment out the postgisgui copy in the bat script 
because that is already installed during the 2.0 installation

Does that sound right to you?
Thanks,
Vishal

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon 
Corporation
Sent: Friday, August 19, 2011 11:27 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] installing 2 versions of postgis

Vishal,

You just need to install the postgis scripts for 1.5 in the database (and copy 
over the postgis-1.5.dll etc in the lib folder).

Both can coexist on same cluster as long as they are in different databases.

If you look at the zip download packages we have the batch file will install 
the postgis in the specified database
PostGIS 1.5: http://www.postgis.org/download/windows/#windbinaries
PostGIS 2.0: 
http://www.postgis.org/download/windows/experimental.php#PostGIS_2_0_0

The only thing to keep in mind is currently they will share the same GEOS and 
Proj.  The PostGIS 2.0 has a slightly newer GEOS (geos 3.3.1 branch and I think 
the PostGIS 1.5.3 we packaged GEOS 3.3.0).  Note: 1.5.2 we packaged with GEOS 
3.2  which won't work with PostGIS 2.0.

If you plan to run both, you should use the GEOS in PostGIS 2.0.0 since the 
PostGIS 2.0.0 GEOS will is  backward compatible with 1.5.2 and 1.5.3.

Let us know if any of this is unclear.

Hope that helps,
Regina
http://www.postgis.us


From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Vishal Mehta
Sent: Friday, August 19, 2011 3:22 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] installing 2 versions of postgis

Hi all,

Ihave ondows7-postgresql9-postgis2.0 installed. I installed postgis 2.0 using 
these instructions:

http://gis4free.wordpress.com/2011/03/10/how-to-install-and-configure-postgis-raster-on-windows/

My qn is how can I have postgis1.5 also installed, say in a different database, 
on the same machine and database cluster?

Thanks,
Vishal

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] installing 2 versions of postgis

[Vishal Mehta]

Hi all,

Ihave ondows7-postgresql9-postgis2.0 installed. I installed postgis 2.0 using 
these instructions:

http://gis4free.wordpress.com/2011/03/10/how-to-install-and-configure-postgis-raster-on-windows/

My qn is how can I have postgis1.5 also installed, say in a different database, 
on the same machine and database cluster?

Thanks,
Vishal

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] connecting to postgis form mapserver

[Vishal Mehta]

David,

I enabled debugging (pl see map file) and got the following error log:
[Thu Aug 18 11:05:26 2011].929000 msPostGISLayerWhichShapes(): Query error. 
Error (ERROR:  function force_2d(geometry) does not exist

Where should I go from here?
Thanks for the error logging how-to,
V

--
LINE 1: select encode(AsBinary(force_collection(force_2d("geom")),'N...
^
HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.
) executing query: select 
encode(AsBinary(force_collection(force_2d("geom")),'NDR'),'hex') as geom,"gid" 
from "wardscleanattributes" where geom && GeomFromText('POLYGON((77.5 
12.8982149362477,77.5 13.0617850637523,77.7 13.0617850637523,77.7 
12.8982149362477,77.5 12.8982149362477))',4326)
[Thu Aug 18 11:05:26 2011].93 msDrawMap(): Image handling error. Failed to 
draw layer named 'wards'.
[Thu Aug 18 11:11:27 2011].929000 msDrawMap(): WMS/WFS set-up and query, 0.000s
[Thu Aug 18 11:11:27 2011].98 msPostGISLayerWhichShapes(): Query error. 
Error (ERROR:  function force_2d(geometry) does not exist
LINE 1: select encode(AsBinary(force_collection(force_2d("geom")),'N...
^
HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.
) executing query: select 
encode(AsBinary(force_collection(force_2d("geom")),'NDR'),'hex') as geom,"gid" 
from "wardscleanattributes" where geom && GeomFromText('POLYGON((77.5 
12.8982149362477,77.5 13.0617850637523,77.7 13.0617850637523,77.7 
12.8982149362477,77.5 12.8982149362477))',4326)
[Thu Aug 18 11:11:27 2011].981000 msDrawMap(): Image handling error. Failed to 
draw layer named 'wards'.
[Thu Aug 18 11:11:27 2011].981000 msFreeMap(): freeing map at 0165CE90.
-
MAP
  IMAGETYPE   PNG
  EXTENT77.5 12.95 77.7 13.01
  SIZE550 450
  IMAGECOLOR  255 255 255
  SHAPEPATH   "../data"
  CONFIG "MS_ERRORFILE" "../ms_error.txt"
  DEBUG 5
  # Start of LAYER DEFINITIONS---
  LAYER 
CONNECTIONTYPE POSTGIS
NAME "wards"
# Connect to a remote spatial database
CONNECTION "host=localhost port=5432 dbname=bangalore user=postgres 
password=xxx"
# Get the lines from the 'geom' column of the 'wards' table 
DATA 'geom from "wardscleanattributes" using srid=4326 using unique gid'
STATUS DEFAULT
TYPE POLYGON 
CLASS
  NAME "wards"
  STYLE
OUTLINECOLOR 255 0 0
  END
END
  END 
   # End of LAYER DEFINITIONS ---
END
-
-Original Message-
From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of David 
Fawcett
Sent: Wednesday, August 17, 2011 7:33 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] connecting to postgis form mapserver

Vishal,

As Ben said, enabling debugging is the best way to figure out what is going on.

There is some pretty good documentation on how to do that at:
http://mapserver.org/optimization/debugging.html

If that doesn't help you, it is probably time to move this over to the 
MapServer list.  (Where Ben and I will also be likely hanging out...)

David.


On Wed, Aug 17, 2011 at 7:44 PM, Ben Madin  
wrote:
> Vishal,
> David has probably nailed this one, but if not or otherwise you might 
> also want to put DEBUG ON into your map and the LAYER definitions (you 
> need both)
> - the debug output from postgis connections is comprehensive if a 
> little complex initially, but very useful for learning.
> It doesn't hurt to explicitly set projections as well, to make sure 
> they match.
> cheers
> Ben
>
>
> On 18/08/2011, at 4:09 AM, Vishal Mehta wrote:
>
> Hi all,
>
> I am testing out mapserver-postgis and a simple test is returning an 
> empty map. Here is my  map file
>
> MAP
>   IMAGETYPE   PNG
>   EXTENT 77.5 12.95 77.7 13.01
>   SIZE    550 450
>   IMAGECOLOR  255 255 255
>   SHAPEPATH   "../data"
>     # Start of LAYER DEFINITIONS---
>   LAYER
> CONNECTIONTYPE POSTGIS
>     NAME "wards"
>     # Connect to a remote spatial database
>     CONNECTION "host=localhost port=5432 dbname=empty 
> user=postgres password=xxx"
>       # Get the lines from the 'geom' column of the 'wards'
> table
>       DATA 'geom from "myschema.

[postgis-users] connecting to postgis form mapserver

[Vishal Mehta]

Hi all,

I am testing out mapserver-postgis and a simple test is returning an empty map. 
Here is my  map file

MAP
  IMAGETYPE   PNG
  EXTENT 77.5 12.95 77.7 13.01
  SIZE550 450
  IMAGECOLOR  255 255 255
  SHAPEPATH   "../data"
# Start of LAYER DEFINITIONS---
  LAYER
CONNECTIONTYPE POSTGIS
NAME "wards"
# Connect to a remote spatial database
CONNECTION "host=localhost port=5432 dbname=empty user=postgres 
password=xxx"
  # Get the lines from the 'geom' column of the 'wards' table
  DATA 'geom from "myschema.wards198" using srid=4326 using 
unique gid'
  STATUS ON
  TYPE POLYGON

CLASS
  NAME "wards"
  STYLE
OUTLINECOLOR 255 0 0
  END
END
  END

  # End of LAYER DEFINITIONS ---
END
---
And the html that calls it
--


my postgis map






   
 
 
   






Other info:
Platform:

-  Windows 7, postgresql9 , postgis 2.0

-  I installed Mapserver separately through the OSGEO4 installer.

-  I can connect to the same database through php

-  I can create simple mapserver apps using shapefiles etc alright 
(e.g. same map file above, calling a shapefile, works fine)

-  Mapserv.exe -v shows that it was installed with postgis support.

-  Also can Mapserver handle postgis Mulipolygon geometry ok?

I did check most of the points here which seemed ok ( i did not check those in 
this list that I don't really know how to in Windows ).
http://trac.osgeo.org/mapserver/wiki/PostGIS

I realize this might be a mapserver mailing list qn, but thought that some of 
you might know the answer...it might be a simple syntax problem that I've 
missed...

Thanks,
Vishal



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Postgis rasters in OpenLayers?

[Vishal Mehta]

Thank you again Pierre,
Vishal

-Original Message-
From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Pierre 
Racine
Sent: Monday, August 15, 2011 11:54 AM
To: PostGIS Users Discussion; dave.po...@pinan.co.uk
Cc: Eric Kemp-Benedict; Douglas Wang
Subject: Re: [postgis-users] Postgis rasters in OpenLayers?

> So I wanted to check if with OpenLayers instead of Google Earth as the 
> client, I could do the same and just have a sparse geospatial stack.

The answer is in your php server. You decide... Everything depends on what is 
supported by the client. You had to develop a server for Google Earth. There 
are plenty of server for OpenLayer. If you develop your own then there should 
be no problem. What you need is to serve JPEG and PostGIS raster ST_AsJPEG() 
will help you to do that. 

> One other qn I had was : Can Geoserver 'speak' with Postgis 2.0 rasters?

See this recent discussion:

http://osgeo-org.1803224.n2.nabble.com/Geoserver-WCS-amp-PostGIS2-0-Raster-support-td6602643.html

Pierre
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Postgis rasters in OpenLayers?

[Vishal Mehta]

Thanks Dave, Pierre,

There were a couple of reasons for my qn: 

For an earlier application, we used postgis as a map repository and 
geoprocessor, php to interact dynamically with the map repository and perform 
gis operations, R to create graphs, and google earth as the visual frontend. 
http://108.195.205.37/kml/DataGrid.kmz

So I wanted to check if with OpenLayers instead of Google Earth as the client, 
I could do the same and just have a sparse geospatial stack. 

Another reason is that we'll need to find a hosting provider and are wondering 
if we can find one with all the capabilities or flexibility to put up 
experimental versions of software and have all these individual software hosted.

One other qn I had was : Can Geoserver 'speak' with Postgis 2.0 rasters?

Thanks again!
V

-Original Message-
From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Dave Potts
Sent: Monday, August 15, 2011 10:59 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Postgis rasters in OpenLayers?

On 15/08/11 18:53, Pierre Racine wrote:

Use geoserver which can make a request to a postgis database and supply data as 
 wfs or wms servers.  You can then use openlayers to access the wfs/wms server
> I don't think there is anything in OpenLayer able to "speak" directly with 
> PostGIS... You need something in between able to deliver tiles. If your php 
> code can do that... But why trying to reinvent a tile server other than 
> MapServer or GeoServer?
>
> Pierre
>
>> -Original Message-
>> From: postgis-users-boun...@postgis.refractions.net 
>> [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of 
>> Vishal Mehta
>> Sent: Monday, August 15, 2011 1:43 PM
>> To: postgis-users@postgis.refractions.net
>> Cc: Eric Kemp-Benedict; Douglas Wang
>> Subject: [postgis-users] Postgis rasters in OpenLayers?
>>
>> Can anyone tell me if Postgis rasters can be displayed in OpenLayers?
>>
>>
>>
>> We are trying to develop a web-gis stack (so far Postggresql - 
>> Postgis - PhP -
>> OpenLayers) on an external server. I'm wondering if we can have 
>> raster functionality as well as display with postgis alone as the map 
>> server...without using Geoserver of Mapserver.
>>
>>
>>
>> Thank you,
>>
>> Vishal
>>
>>
>>
>> Vishal K. Mehta <http://sei-us.org/about/staff_person/19> , Ph.D.
>>
>> Staff Scientist,
>>
>> Stockholm Environment Institute-US
>>
>> 400 F St, Davis, CA 95616
>>
>> http://sei-us.org/
>>
>>
>>
>>
>>
>>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Postgis rasters in OpenLayers?

[Vishal Mehta]

Can anyone tell me if Postgis rasters can be displayed in OpenLayers?

We are trying to develop a web-gis stack (so far Postggresql - Postgis - PhP 
-OpenLayers) on an external server. I'm wondering if we can have raster 
functionality as well as display with postgis alone as the map server...without 
using Geoserver of Mapserver.

Thank you,
Vishal

Vishal K. Mehta, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616
http://sei-us.org/



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] spatial indices on two geometry or rast columns

[Vishal Mehta]

Superb.
That's clear to me.
Thanks again,
V

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Bryce L 
Nordgren
Sent: Thursday, August 11, 2011 2:16 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] spatial indices on two geometry or rast columns



postgis-users-boun...@postgis.refractions.net
 wrote on 08/11/2011 08:56:44 PM:

> I am not planning to cearch using utm coordinates. The main reason I
> would create a utm geom, was so that I could calculate areas,
> distances in sq km easily without having to include nested
> ST_TRANSFORM statements. In this case would it be useful to have
> another index on the utm?

I'd say that a good rule of thumb would be:

If the WHERE clause of your SELECT statement names a geometry column, make an 
index on that column. If the column isn't in the WHERE clause, don't bother. :)

Bryce
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] spatial indices on two geometry or rast columns

[Vishal Mehta]

Thanks Bryce,

That's useful.

I am not planning to cearch using utm coordinates. The main reason I would 
create a utm geom, was so that I could calculate areas, distances in sq km 
easily without having to include nested ST_TRANSFORM statements. In this case 
would it be useful to have another index on the utm?

Cheers
Vishal

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Bryce L 
Nordgren
Sent: Thursday, August 11, 2011 1:38 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] spatial indices on two geometry or rast columns



postgis-users-boun...@postgis.refractions.net
 wrote on 08/11/2011 07:16:22 PM:

> I'd like to transform these layers to UTM (EPSG 32643). I was
> thinking of creating a new geometry column for each table and
> ALTER TABLE wards ADD column geom_utm geometry;
> UPDATE wards SET geom_utm = ST_TRANSFORM(geom,32643);
>
> Should I, (and can I) create another gist index on the transformed geometry?

If you're planning on searching using utm coordinates, I'd make another index. 
If the utm version is just there for reference, I wouldn't bother.

> The same qn applies on a raster table I have called srtm_table -
> should I and can I add
> -  a new 'rast' column called rast_utm with transformed SRID
> in similar fashion above, and then add a gist index on the rast_utm column?
>
> CREATE INDEX "srtm_table_rast_gist_idx" ON "public"."srtm_table"
> USING GIST (st_convexhull(rast_utm));

This isn't really a different question than the above, since 
st_convexhull(raster) produces a geometry. :) I'd say the same thing: if you're 
going to search using utm coordinates, make a utm index; otherwise, no.


Bryce
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] spatial indices on two geometry or rast columns

[Vishal Mehta]

Hi all,

I have some vector layers in  a postgis database in EPSG 4326. Spatial indices 
have been built using GIST on the same (EPSG 4326) geometries on a geometry 
column called geom.

I'd like to transform these layers to UTM (EPSG 32643). I was thinking of 
creating a new geometry column for each table and

ALTER TABLE wards ADD column geom_utm geometry;

UPDATE wards SET geom_utm = ST_TRANSFORM(geom,32643);

Should I, (and can I) create another gist index on the transformed geometry?

The same qn applies on a raster table I have called srtm_table - should I and 
can I add

-  a new 'rast' column called rast_utm with transformed SRID in similar 
fashion above, and then add a gist index on the rast_utm column?

CREATE INDEX "srtm_table_rast_gist_idx" ON "public"."srtm_table" USING GIST 
(st_convexhull(rast_utm));

Thanks,
Vishal

Vishal K. Mehta, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616
http://sei-us.org/



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] help with backing up and restoring a postgisdatabase (Raster comments)

[Vishal Mehta]

Thanks Regina and Pierre,

When you have a chance, pl look at my qns on the rid's at the end...

Cheers,
Vishal

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon 
Corporation
Sent: Friday, August 05, 2011 2:13 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] help with backing up and restoring a 
postgisdatabase (Raster comments)

Vishal,
P.S. - your raster notes are a good start. Haven't had a chance to read thru to 
see if I can help with your questions at the end.

I would add a link to the PostGIS Raster manual section of the PostGIS docs 
since that includes descriptions and examples for most of the functions.  It's 
still not quite complete but about 90% so.

http://www.postgis.org/documentation/manual-svn/RT_reference.html

Regina
http://www.postgis.us<http://www.postgis.us/>



From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Vishal Mehta
Sent: Thursday, August 04, 2011 8:11 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] help with backing up and restoring a postgisdatabase

Hi all,

I am working with windows7/ postgres9.0.4/postgis2.0 which I recently installed 
and started testing like so:
http://algoesalgo.wordpress.com/tag/postgis2-0/

my previous experience has been with postgres8.3/postgis1.5, and I have never 
before now tried to backup and restore a postgres/postgis database

I am running into several errors when using pg_dump and pg_restore, and the 
postgres documentation has raised some doubts in my mind as well. My colleagues 
and I are trying to develop an application on one server which we know we'll 
need to migrate next year, so we want to nail down the best way to do this. 
Before I describe the kinds of errors specific to certain attempts I've made 
(its quite confusing to keep track of for me), I'd like to know if there are 
best practices regarding:


-  Pg_dump qns:

is it best to skip owner and priviliges (if say I want to havemy colleague 
recreate my database sitting on localhost on my machine)

is it best to backup data only, skipping schema (same as above)

any other flags that are best set a certain way?





-   Psql dbnamehttp://sei-us.org/about/staff_person/19>, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616
http://sei-us.org/



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] help with backing up and restoring apostgis database

[Vishal Mehta]

Regina and Leo,
Thanks for your reply.

Just before you wrote, I managed to restore a postgis database into a new empty 
database, by using template_postgis20 as the template for the empty database - 
I guess that effectively did what you suggested, which is to install postgis 
into the new database.

Fyi, how I installed postgis2.0 on windows is in my blogpost from earlier:
http://algoesalgo.wordpress.com/tag/postgis2-0/ following advice from various 
other blogs that are also listed there.

also fyi, I am using a windows machine right now- would prefer using my Ubuntu 
box, but I am not comfortable enough on linux to compile from scratch.

For now it seems to have worked. It would be better to install postgis fresh 
into a new database but I don't know exactly how on windows...I guess I could 
follow the same procedures as in my original installation.

Thanks,
Vishal

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon 
Corporation
Sent: Friday, August 05, 2011 2:08 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] help with backing up and restoring apostgis 
database

Vishal,

Did you install PostGIS in the new database you created before trying to 
restore? From your errors doesn't sound like you did that.  Please give that a 
try and let us know if you have trouble.

We have instructions documented here:

http://www.postgis.org/documentation/manual-svn/postgis_installation.html#hard_upgrade

For windows, the included batch script should allow you to create a new 
spatially enabled database.  Just have to change the lines to fit your 
platform.  I've just been creating new databases with the script instead of 
using a template_postgis since postgis 2.0 is still in flux.  So though the 
script has a template_postgis specified, you can use to build any postgis 
enabled database by changing the name of the database in the batch script.

We compiled a new windows build yesterday so is very up to date

http://www.postgis.org/download/windows/experimental.php#PostGIS_2_0_0

As far as your permission questions, probably best to ask that on the 
PostgreSQL general group.  For PostgreSQL 9.0, we usually just use the new 
default permissions features to set the permissions of the database and schemas 
before we restore anything.

Hope that helps,
Regina and Leo
http://www.postgis.us<http://www.postgis.us/>



From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Vishal Mehta
Sent: Friday, August 05, 2011 2:50 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] help with backing up and restoring apostgis 
database

Hi all,

I can provide a specific workflow describing my problems with backing up and 
restoring a postgis2.0 database.

Information:

-  Database I want to restore is called 'bangalore'; I want to restore 
it into an empty database called 'empty'

-  There are 3 schemas under this database

o   'Public' was created on creation of Bangalore database. This contains both 
raster and vector tables

o   'Topology' was also created automatically (this seems to be new in 
postgis2.0 vs postgis 1.5 and I don't know about it)

o   'myschema' was a chema I created which has 4 vector tables which I would 
like to test for this backup.

What I did:

-  Created an empty database in the psql console:

o   CREATE DATABASE empty TEMPLATE=template0;

-  Backup using pg_dump like so:

o   Pg_dump.exe -Fc -n "myschema" -v bangalore > "backupfc.dump"

-  Tried to restore like so:

o   Pg_restore.exe -dbname "empty" -verbose "backupfc.dump"
I got errors after myschema was created, upon the first table creation: the 
first few error lines are:

Error while processing TOC:
Error from TOC entry 2551: 1251 etc
Could not execute query type "public.geometry does not exist


When I investigated the sql in the backup (froma  separate plain sql dump) I 
see this:

CREATE TABLE borewells (
gid integer NOT NULL,
"UTM_X_" double precision,
"UTM_Y_" double precision,
"Code_No" character varying(254),
"Water_Leve" double precision,
geom public.geometry(Point,4326)
);

Its failing on the geom column. I am not an expert with databases but clearly 
there is no geometry table in the public schema (which I am not wanting to 
backup anyway).

My best guess is that, in postgis1.5 there was a separate geometry columns 
table. In postgis 2.0 there is a Views thing with geometry_columns in there- 
this is also new to me.  Has this got something to do with it?

Would appreciate some help. I am not a software developer- I just recognize the 
potential in postgis and have been trying to use it for awhil

Re: [postgis-users] help with backing up and restoring a postgis database

[Vishal Mehta]

Hi all,

I can provide a specific workflow describing my problems with backing up and 
restoring a postgis2.0 database.

Information:

-  Database I want to restore is called 'bangalore'; I want to restore 
it into an empty database called 'empty'

-  There are 3 schemas under this database

o   'Public' was created on creation of Bangalore database. This contains both 
raster and vector tables

o   'Topology' was also created automatically (this seems to be new in 
postgis2.0 vs postgis 1.5 and I don't know about it)

o   'myschema' was a chema I created which has 4 vector tables which I would 
like to test for this backup.

What I did:

-  Created an empty database in the psql console:

o   CREATE DATABASE empty TEMPLATE=template0;

-  Backup using pg_dump like so:

o   Pg_dump.exe -Fc -n "myschema" -v bangalore > "backupfc.dump"

-  Tried to restore like so:

o   Pg_restore.exe -dbname "empty" -verbose "backupfc.dump"
I got errors after myschema was created, upon the first table creation: the 
first few error lines are:

Error while processing TOC:
Error from TOC entry 2551: 1251 etc
Could not execute query type "public.geometry does not exist


When I investigated the sql in the backup (froma  separate plain sql dump) I 
see this:

CREATE TABLE borewells (
gid integer NOT NULL,
"UTM_X_" double precision,
"UTM_Y_" double precision,
"Code_No" character varying(254),
"Water_Leve" double precision,
geom public.geometry(Point,4326)
);

Its failing on the geom column. I am not an expert with databases but clearly 
there is no geometry table in the public schema (which I am not wanting to 
backup anyway).

My best guess is that, in postgis1.5 there was a separate geometry columns 
table. In postgis 2.0 there is a Views thing with geometry_columns in there- 
this is also new to me.  Has this got something to do with it?

Would appreciate some help. I am not a software developer- I just recognize the 
potential in postgis and have been trying to use it for awhile..

Even some general advice towards abandoning postgres9.0/postgis2.0 and going 
back to posgres8.3/postgis1.5 could help.

Thanks,
Vishal









From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Vishal Mehta
Sent: Thursday, August 04, 2011 5:11 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] help with backing up and restoring a postgis database

Hi all,

I am working with windows7/ postgres9.0.4/postgis2.0 which I recently installed 
and started testing like so:
http://algoesalgo.wordpress.com/tag/postgis2-0/

my previous experience has been with postgres8.3/postgis1.5, and I have never 
before now tried to backup and restore a postgres/postgis database

I am running into several errors when using pg_dump and pg_restore, and the 
postgres documentation has raised some doubts in my mind as well. My colleagues 
and I are trying to develop an application on one server which we know we'll 
need to migrate next year, so we want to nail down the best way to do this. 
Before I describe the kinds of errors specific to certain attempts I've made 
(its quite confusing to keep track of for me), I'd like to know if there are 
best practices regarding:


-  Pg_dump qns:

is it best to skip owner and priviliges (if say I want to havemy colleague 
recreate my database sitting on localhost on my machine)

is it best to backup data only, skipping schema (same as above)

any other flags that are best set a certain way?





-   Psql dbnamehttp://sei-us.org/about/staff_person/19>, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616
http://sei-us.org/



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] help with backing up and restoring a postgis database

[Vishal Mehta]

Hi all,

I am working with windows7/ postgres9.0.4/postgis2.0 which I recently installed 
and started testing like so:
http://algoesalgo.wordpress.com/tag/postgis2-0/

my previous experience has been with postgres8.3/postgis1.5, and I have never 
before now tried to backup and restore a postgres/postgis database

I am running into several errors when using pg_dump and pg_restore, and the 
postgres documentation has raised some doubts in my mind as well. My colleagues 
and I are trying to develop an application on one server which we know we'll 
need to migrate next year, so we want to nail down the best way to do this. 
Before I describe the kinds of errors specific to certain attempts I've made 
(its quite confusing to keep track of for me), I'd like to know if there are 
best practices regarding:


-  Pg_dump qns:

is it best to skip owner and priviliges (if say I want to havemy colleague 
recreate my database sitting on localhost on my machine)

is it best to backup data only, skipping schema (same as above)

any other flags that are best set a certain way?





-   Psql dbnamehttp://sei-us.org/about/staff_person/19>, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616
http://sei-us.org/



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users