Re: [postgis-users] Pg_wrapper error

[Lee Hachadoorian]

On Fri, Oct 26, 2012 at 11:27 AM, José Pedro Santos
 wrote:
> I have the executables. When I try that (only shp2pgsql before I make the
> connection with pg_wrapper) I can get the help commands. But now don't
> because I enable the file with pg_wrapper, how can undo that?
>

When you created the symlink using the ln command, it would have
overwritten the executables. If you backed them up first, delete the
symlink and restore the executable from backup. If you didn't back
them up, you'll have to reinstall PostGIS.

--Lee

-- 
Lee Hachadoorian
Asst Professor of Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Pg_wrapper error

[Lee Hachadoorian]

On Fri, Oct 26, 2012 at 10:51 AM, José Pedro Santos
 wrote:
>
> Hi,
>
> raster2pgsql don't work also shp2pgsql don't work (I have the Postgis 2.0
> installed in Ubuntu 12.04). I just enable the function but seems that the
> pg_wrapper don't work.
>
> I have all the raster2... and shp2... in /usr/bin but don't work when I try
> to call them in the shell.

When you say you have raster2pgsql and shp2pgsql in /usr/bin, do you
have executables or just the symlinks you created? If you call
shp2pgsql without arguments do you get the help commands? Can you
output it to a file? That is instead of:

shp2pgsql /home/user/Downloads/Africa_Boundaries/africa.shp
teste2.africa | psql -d MyDatabase

can you

shp2pgsql /home/user/Downloads/Africa_Boundaries/africa.shp
teste2.africa > /home/user/Downloads/import_africa.sql

?

--Lee

-- 
Lee Hachadoorian
Asst Professor of Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Pg_wrapper error

[Lee Hachadoorian]

On Thu, Oct 25, 2012 at 11:26 AM, José Pedro Santos
 wrote:
> Dear all,
>
> I have one problem when I try to load raster to Postgis with the script
> raster2pgsql from the shell.
>
> I have this error: Error: pg_wrapper: invalid command name
>
> Before this error (I have Postgis version 2.0) I can't use the command
> (raster2pgsql) in the shell (was not found) but i enable it with this
> expression:
>
> sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/bin/raster2pgsql
>
> After that the error about pg_wrapper appear.
>
> Any suggestion?
>
> Best Regards and thanks,
> Jose Santos

raster2pgsql and shp2pgsql are not links to pg_wrapper. The error you
are getting is exactly what I get if I try to call pg_wrapper
directly, but raster2pgsql works because it is installed and exists as
an executable in /usr/bin.

Remove the link to pg_wrapper figure out why raster2pgsql isn't
installed. How did you install PostGIS 2? What OS? Is shp2pgsql
installed and working? If it is, is raster2pgsql available in the same
directory? etc.

--Lee

-- 
Lee Hachadoorian
Asst Professor of Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Upgrade table to PostGIS 2.0 structure

[Lee Hachadoorian]

I finally got around to upgrading from Postgres 8.4 / PostGIS 1.5 to 
Postgres 9.1 / PostGIS 2.0. Did full dump and restore of my database 
using postgis_restore.pl. Noticed that the tables remained in PostGIS 
1.5 format. I tested the following SQL on a table:


ALTER TABLE alis.geo_tracts
ALTER COLUMN the_geom TYPE geometry(MULTIPOLYGON, 26918),
DROP CONSTRAINT enforce_dims_the_geom,
DROP CONSTRAINT enforce_geotype_the_geom,
DROP CONSTRAINT enforce_srid_the_geom
;

This alters a PostGIS 1.5 geometry column to use a typmodded PostGIS 2.0 
geometry column, and removes the no-longer-used constraints. After 
altering the table, it appears correctly in geometry_columns view, and I 
successfully added it as a layer to a Quantum GIS project.


This seems to work, but is there anything I need to know about this? Is 
there (a) anything else that needs to be changed to make the table 
conform to the expected PostGIS 2.0 structure, or (b) any reason why 
this is *not* a good way to upgrade these tables to the new format?


Best,
--Lee

--
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu

--
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu

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

[postgis-users] Problems Accessing prj2epsg.org

[Lee Hachadoorian]

This is not specifically a PostGIS question, but I often use
prj2epsg.org to identify SRIDs when I import shapefiles. For a while
now (a few weeks, maybe?) I've just been getting a completely blank
page when I go there. Not that the server is not found, it's there,
but seems to be serving…nothing. Google isn't turning up any news
related to this.

Anyone know anything about this?

Best,
--Lee

-- 
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS

[Lee Hachadoorian]

On Thu, Jun 28, 2012 at 1:41 PM, Mathieu Basille
 wrote:
>
> I wonder whether the postgis_restore script would not alter the DB,
> especially since I still use an old PostGIS version where raster_columns was
> not yet a view... This is why it made sense to me to dump the whole DB again
> at the end of the process (to have a clean DB). But I might be wrong here.
>
> The main reason about starting the upgrade with Debian first is that I
> perfectly know how to restore the system (PostgreSQL/PostGIS + DB) in its
> current state with Debian. Which means that I can break it all, I will be
> able to come up with a functional system anyway (from a backup of the /main
> directory). I understand your point of view about switching OSes though.
> Still, I'm so much more familiar with Debian vs. Windows that I would rather
> lose some time in the upgrade process than running critical operations with
> Windows (i.e. upgrading PostGIS).


Ah, so the idea is that you need to accommodate changes in how PostGIS
stores things and feel more comfortable using Debian for that part of
the process. Makes sense. Need to do that myself to move from 1.5 to
2.0. Still think it makes sense to do the restore to a separate Debian
machine (which it sounds like you agree with) that can serve as an
fallback if the Windows install is problematic.

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

Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS

[Lee Hachadoorian]

On Thu, Jun 28, 2012 at 10:38 AM, Mathieu Basille
 wrote:
> Le 28/06/2012 10:32, Sandro Santilli a écrit :
>
>> On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote:
>>>
>>> Dear list,
>>>
>>> I need to proceed with a sensitive (and unfortunate) migration of
>>> the computer which currently runs PostGIS from Debian to Windows. I
>>
>> You'll need to invoke pg_dump using the -Fc (custom format) switch,
>> which gives you not an SQL but an indexed dump, needed for skipping
>> the parts you won't want to restore. postgis_restore.pl will take
>> care of the restore (and skipping).
>
> Dear Sandro,
>
> Thanks for the additional information. I didn't understand the specifics of
> the -Fc switch (maybe something to add in the doc?). It seems I don't have
> to worry too much with the PostgreSQL/PostGIS upgrade.

Manthieu,

-Fc specifies "custom format", which is basically Postgres' own dump
format. It is smaller and faster to dump/restore than plain text. It
also allows you at restore to selectively choose which tables or
schemas you want to restore. But it can only restore to a Postgres
database. Plain text, which builds the restore as SQL CREATE and
INSERT statements, is slower, but can often be restored to non-
Postgres databases. See
http://www.postgresql.org/docs/8.4/static/app-pgdump.html.

>> And yes, I'd do the upgrade on Unix first.
>> Once you go to windows you'll be alone in the dark ...
>
> This is actually my main concern... I'm very familiar and happy with Debian,
> and not at all with Windows... Anyway, the migration is not my call! I will
> thus proceed with the upgrade first on Debian, and later switch to Windows.

pg_dump is designed to restore between different Postgres platforms
and from lower to higher versions. I have not done a Linux→Windows
migration, but I have done a Windows→Linux migration, and there were
no problems or incompatibilities in switching OSes. I don't see the
point of restoring to a machine you are going to wipe anyway, and
since the database structure and content won't have changed (even
though the version has), I believe you will end up with exactly the
same dump file.

If you wanted to be paranoid about it, rather than restoring to the
machine you will wipe, maybe you have some other old piece of
equipment running Debian (or that you can install Debian on) that you
can restore to temporarily. Obviously, you wouldn't want to have
thousands of users hitting it, but Postgres/PostGIS runs quite well on
desktop-class hardware. This would also preserve access to your data
while you are setting up Windows, in case something goes wrong or it
takes longer than you expect.

--Lee

-- 
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Organizations relying on PostGIS

[Lee Hachadoorian]

I'm going to be giving a talk on PostGIS to a local Postgres group
next week. I would like to mention some large clients and/or
interesting uses to my audience, but the Case Studies section on the
PostGIS website is a bit dated. Does anyone want to call attention to
any enterprise, government, or otherwise interesting implementations?

Best,
--Lee

-- 
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Diff. Between 2 tables

[Lee Hachadoorian]

  
  
On 06/07/2012 09:14 AM, Hemin Tofiq wrote:

  Thank you,
I have 2 tables with the same structure, so we don't need to create alias
for the same table, however that is fine with updated rows, how can I get
newly created rows?

Regards,
Hemin



I assume you mean you have added a new row to one table but not the
"twin" table. 

Comparing on a unique key would look like:

SELECT * 
FROM table1
    LEFT JOIN table2 ON (table1.unique_key = table2.unique_key)
WHERE table2.unique_key IS NULL;

I think you said you want to be able to do this on geometries. I
haven't tried this, but I assume something like this might work:

SELECT * 
FROM table1
    LEFT JOIN table2 ON (ST_Equals(table1.the_geom, table2.the_geom))
WHERE table2.the_geom IS NULL;

Just make sure to read the docs on ST_Equals vs. ST_OrderingEquals
so that you know which comparison you want.


http://postgis.refractions.net/documentation/manual-1.5/ST_Equals.html

http://postgis.refractions.net/documentation/manual-1.5/ST_OrderingEquals.html

Best,
    --Lee

-- 
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu

  

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

Re: [postgis-users] Ubuntu Postgis and Shp2pgsql

[Lee Hachadoorian]

On Mon, May 28, 2012 at 10:25 AM, Paolo Cavallini  wrote:
> Il 28/05/2012 16:23, Martin Fafard ha scritto:
>> Hi
>>
>> Quantum GIS is a good way to load shape in Postgis (SPIT extension)
>
> SPIT is obsolete. Try DB Manager.
> All the best.

José,

PostGIS will install shp2pgsql, a command line importer. There is also
a GUI, which works standalone or as a pgAdmin plugin (though in
earlier PostGIS versions you had to jump through some hoops to get the
pgAmin integration--I don't know if that has improved in 2.0).

Check out the PostGIS manual
(http://postgis.refractions.net/documentation/manual-2.0/using_postgis_dbmanagement.html#id2811268)
or BostonGIS (http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide_20.bqg)
for a tutorial.

There are additional options. ogr2ogr is a very flexible commandline
tool which allows converting between several different GIS data
formats. shp2pgsql is a one-trick pony, but more straightforward to
use. SPIT (mentioned above) is a GUI over ogr2ogr, and its major
limitation was keeping case in identifiers like field names.

Paolo,

I looked in DB Manager and don't see the import functionality. Where is it?

--Lee
-- 
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Tools to change the encoding of the SHP/DBF files (from Latin1 to UTF-8)

[Lee Hachadoorian]

On Thu, May 10, 2012 at 5:39 PM, Mac Wind  wrote:
> Hi Sandro. Thank you very much for your answer.
>
> With that parameter (-W), am I changing the encoding of the DB or I am
> merely indicating the encoding of the source?
>
> Regards.

The latter. I don't think it's possible to change the server encoding
once the database is created. This switch has the same effect as

SET client_encoding = 'LATIN1';

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

Re: [postgis-users] Removed double quotes from column and table names

[Lee Hachadoorian]

This option is not available via SPIT, but ogr2ogr does allow it as a layer
creation option:

"LAUNDER: This may be "YES" to force new fields created on this layer to
have their field names "laundered" into a form more compatible with
PostgreSQL. This converts to lower case and converts some special
characters like "-" and "#" to "_". If "NO" exact names are preserved. The
default value is "YES". If enabled the table (layer) name will also be
laundered." (http://www.gdal.org/ogr/drv_pg.html)

Also shp2pgsql defaults to forcing lower case names (the correct choice,
IMHO), but has an option to specify preserving case.

Best,
--Lee


On Mon, Mar 19, 2012 at 6:58 AM, Simon Greener
wrote:

> Birgit,
>
> I realise that I can do this manually. I was looking for a method for doing
> this at the point of loading the shapefile into PostGIS using QGIS. I will
> have a look at ogr2ogr to see if that helps.
>
> Thanks
> regards
> Simon
>
> On Mon, 19 Mar 2012 21:00:27 +1100, Birgit Laggner <
> birgit.lagg...@vti.bund.de> wrote:
>
>  Hi Simon,
>>
>> I would think RENAME would do what you want:
>>
>> ALTER TABLE schema."TableName" RENAME TO tablename;
>>
>> ALTER TABLE schema.tablename RENAME COLUMN "ColumnName" TO columnname;
>>
>> Hope that helps,
>>
>> Birgit.
>>
>>
>> Am 19.03.2012 06:03, schrieb Simon Greener:
>>
>>> Is there any way to remove the double quotes created around a table or
>>> its column names?
>>> My names do NOT include spaces but do include mixed case. I wish to
>>> remove the quotes and
>>> lower case all names.
>>> How can this be done?
>>> Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file
>>> the names are mixed case.
>>> I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to
>>> tell it to lower case all names.
>>> S
>>>
>> __**_
>> postgis-users mailing list
>> postgis-users@postgis.**refractions.net
>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users
>>
>>
>
> --
> 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: si...@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@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] Best Practices for Custom SRID/proj4text

[Lee Hachadoorian]

On Sun, 05 Dec 2010 20:59:25 +1300, Robert Coup wrote:

> From the EPSG docs:
> 
> The OGP Geodesy Subcommittee has reserved the integer range 0 to 32767
> for
>> use as codes. As of dataset version 6.3, the integer range from
 
> We started at 800,000 iirc.
> 
> If there's a reasonable amount of data using the production, submit the
> documentation to EPSG and it should be officially included in the next
> release, they're pretty open to receiving new projections.
> 
> Rob :)

Thanks,
--Lee

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

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

[postgis-users] Best Practices for Custom SRID/proj4text

[Lee Hachadoorian]

I need to add an SRID for a custom projection (specifically a Lambert 
Conformal Conic used by New York City Dept of City Planning for their 
publicly available geographic data). Does everyone just start numbering 
from 1 in their local database, or are there any guidelines or does 
anyone have any tips for assigning numbers?

--Lee

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

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

Re: [postgis-users] ESRI Geodatabases, relationship classes and PostGIS

[Lee Hachadoorian]

Juan,

You should have a table called GDB_RelClasses, which has columns named
(among others) OriginPrimaryKey, DestPrimaryKey, OriginForeignKey,
DestForeignKey.  In theory, you could iterate this table and create
the SQL for the necessary foreign key constraints.  The payoff may be
slight if you only need to do this once, but at least if you open
GDB_RelClasses and print it out you'll have all your relationships
listed in one place to refer to as you recreate them in Postgres.  (It
beats opening the properties dialog for each relationship class in
ArcCatalog.)

The SQL might (not tested) look something like:

ALTER TABLE destination_table ADD CONSTRAINT constraint_name_fkey
FOREIGN KEY column_name REFERENCES origin_table (id_column)

I don't see anything in the ESRI documentation that documents the
structure (other than field names) of GDB_RelClasses, so unless you
find something, it will take some legwork to figure it out.  As an
example, I created a one-to-many relationship, and this appears as
Cardinality=2.  The SQL would be as above.  For a one-to-one
relationship (Cardinality=1?), you would have to make the foreign key
column in destination_table be a primary key as well.  If the
relationship is composite (IsComposite=1 in GDB_RelClasses), you will
need to use the ON DELETE CASCADE phrase.

An important question for those who know more about PostGIS than me is
whether there's is anything like the spatial update behavior built
into composite relationships.  For a composite relationship, if the
origin feature is moved or rotated, the destination feature will be
moved or rotated as well.  Is there a way for Juan to implement this
in PostGIS (assuming he needs it)?

Info on foreign keys in Postgres:
http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ESRI Geodatabases, relationship classes and PostGIS

[Lee Hachadoorian]

Yes, ArcCatalog creates a standard Access database table, but enforces
the creation of a unique index called OBJECTID (which is not, however,
actually a primary key).  So what Regina describes should work.

There's an ogr2ogr cheatsheet at BostonGIS
(http://www.bostongis.com/?content_name=ogr_cheatsheet).

I'm not sure about the relationship classes question, although my
suspicion is that the conversion works table-by-table on data (spatial
and non-spatial) only.  Since the relationship classes are not stored
in the feature class or non-spatial table itself (they are stored in a
table called GDB_RelClasses), I suspect that ogr2ogr will not do
anything to convert them.  Can someone confirm?

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Collecting Polygons

[Lee Hachadoorian]

Regina & Kevin,

I actually do need the dissolved geometries, and the data set is small
enough that there was no speed issue with ST_Union.  But I'll file
away the info about ST_Collect.

Thanks both for your help,
--Lee
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Collecting Polygons

[Lee Hachadoorian]

Interesting.  Very clear now that you showed me the SQL.  This was not
really clear to me from the docs.

Thanks,
--Lee

On Fri, Jun 27, 2008 at 3:00 PM, Kevin Neufeld <[EMAIL PROTECTED]> wrote:
> It merges all geometries you supply it.  If you supply geometries from
> different tables, then yes.
>
> SELECT ST_Union(geom)
> FROM (
>  SELECT geom FROM mytable1 WHERE ...
>  UNION ALL
>  SELECT geom FROM mytable2 WHERE ...
> ) AS foo;
>
> -- Kevin
>
> Lee Hachadoorian wrote:
>>
>> Thanks, Kevin, that worked.  I saw ST_Union, but I thought it was for
>> joining polygons from different spatial tables.  Does it do that also?
>>
>> --Lee
>>
>> On Fri, Jun 27, 2008 at 2:19 PM, Kevin Neufeld <[EMAIL PROTECTED]>
>> wrote:
>>>
>>> Hi Lee,
>>>
>>> If you want to merge several polygons together, dissolving them into a
>>> single polygon, then you'll want to use ST_Union() instead of
>>> ST_Collect().
>>>
>>> ST_Union() - returns the point set union of all input geometries,
>>> ST_Collect() - is a simple aggregate function that concatenates
>>> geometries
>>> together, ie a bunch of polygons to a multipolygon.
>>>
>>> -- Kevin
>>>
>>> Lee Hachadoorian wrote:
>>>>
>>>> Upgraded to 1.3.3 to get rid of previous problem with ST_Collect, but
>>>> I'm still not getting the output I want.  What I want to do is take
>>>> several polygons and mash them together into one polygon.  The SQL I
>>>> am using is:
>>>>
>>>> SELECT ST_Collect(the_geom)
>>>> FROM proj_mortgage.geo_hh_race
>>>> WHERE pct_hispanic > 50;
>>>>
>>>> But ST_Collect returns a GEOMETRYCOLLECTION, and I want a polygon or
>>>> multipolygon.  Do I need to do something with the GEOMETRYCOLLECTION
>>>> returned by ST_Collect, or is ST_Collect the wrong function to use in
>>>> the first place?
>>>>
>>>> Thanks,
>>>> Lee Hachadoorian
>>>> PhD Student, Geography
>>>> Program in Earth & Environmental Sciences
>>>> CUNY Graduate Center
>>>> ___
>>>> 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
>



-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Collecting Polygons

[Lee Hachadoorian]

Thanks, Kevin, that worked.  I saw ST_Union, but I thought it was for
joining polygons from different spatial tables.  Does it do that also?

--Lee

On Fri, Jun 27, 2008 at 2:19 PM, Kevin Neufeld <[EMAIL PROTECTED]> wrote:
> Hi Lee,
>
> If you want to merge several polygons together, dissolving them into a
> single polygon, then you'll want to use ST_Union() instead of ST_Collect().
>
> ST_Union() - returns the point set union of all input geometries,
> ST_Collect() - is a simple aggregate function that concatenates geometries
> together, ie a bunch of polygons to a multipolygon.
>
> -- Kevin
>
> Lee Hachadoorian wrote:
>>
>> Upgraded to 1.3.3 to get rid of previous problem with ST_Collect, but
>> I'm still not getting the output I want.  What I want to do is take
>> several polygons and mash them together into one polygon.  The SQL I
>> am using is:
>>
>> SELECT ST_Collect(the_geom)
>> FROM proj_mortgage.geo_hh_race
>> WHERE pct_hispanic > 50;
>>
>> But ST_Collect returns a GEOMETRYCOLLECTION, and I want a polygon or
>> multipolygon.  Do I need to do something with the GEOMETRYCOLLECTION
>> returned by ST_Collect, or is ST_Collect the wrong function to use in
>> the first place?
>>
>> Thanks,
>> Lee Hachadoorian
>> PhD Student, Geography
>> Program in Earth & Environmental Sciences
>> CUNY Graduate Center
>> ___
>> 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
>



-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Collecting Polygons

[Lee Hachadoorian]

Upgraded to 1.3.3 to get rid of previous problem with ST_Collect, but
I'm still not getting the output I want.  What I want to do is take
several polygons and mash them together into one polygon.  The SQL I
am using is:

SELECT ST_Collect(the_geom)
FROM proj_mortgage.geo_hh_race
WHERE pct_hispanic > 50;

But ST_Collect returns a GEOMETRYCOLLECTION, and I want a polygon or
multipolygon.  Do I need to do something with the GEOMETRYCOLLECTION
returned by ST_Collect, or is ST_Collect the wrong function to use in
the first place?

Thanks,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Collecting Polygons

[Lee Hachadoorian]

Thank you!  Will upgrade next week when I'm back in the office.

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Collecting Polygons

[Lee Hachadoorian]

Hello,

I'm having a problem using ST_Collect.  Every time (except once) that
I try it, it crashes my Postgres server.  I am running PostGIS 1.3.2
on Postgres 8.3.0 on WindowsXP.

The SQL I am trying to run is:

SELECT
ST_COLLECT(the_geom) as new_geom,
improve_2001
FROM
proj_mortgage.geo_li_basics_by_tract
WHERE improve_2001 < 10
GROUP BY improve_2001
;

The GROUP BY gives me 10 classes, and the aggregate function apart
from ST_Collect works fine.  For example, this SQL works:

SELECT
sum(ST_Area(the_geom)) as tract_area,
improve_2001
FROM
proj_mortgage.geo_li_basics_by_tract
WHERE improve_2001 < 10
GROUP BY improve_2001
;

Any ideas how I can troubleshoot this?

Thanks,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] info

[Lee Hachadoorian]

Hi Chris,

I ran into the same problem on Ubuntu 7.10.  Lwpostgis.sql would not run 
to completion, and all commands would be rolled back.  Believe it or not 
I got through it by opening lwpostgis.sql and running each block of 
commands separately.  I got through everything without ever finding out 
what it had been stumbling on, and my PostGIS install is up and running.


Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center


Chris H wrote:

Greets:

Currently working on installing osm/mapnik support for gpsdrive. This requires 
postgres and postgis integration. As this is a first run with all of this I'm 
rather new to postgis. 

Was wondering if anyone could offer some insight or advise on how to diagnose 
the following problem. The instruction set that I'm following reads as 
follows:


$> su
#> su - postgres
$> 

As the postgres user run the following to create the database:

NOTE:   Replace username with the user that will be using mapnik.

$> createuser username
$> createdb -E UTF8 -O username gis
$> createlang plpgsql gis

This all works fine. No issues here. However the next set of commands barf 
consistently. 


4.1 As the postgresql super user:

NOTE:   Replace username with the user that will be using mapnik.

$> psql -d gis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
$> echo "ALTER TABLE geometry_columns OWNER TO username; ALTER TABLE 
spatial_ref_sys OWNER TO username;"  | psql -d gis

$> exit
#> exit

The line "psql -d gis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql" 
returns the following:


psql:/usr/share/postgresql-8.3-postgis/lwpostgis.sql:1557: ERROR:  current 
transaction is aborted, commands ignored until end of transaction block
psql:/usr/share/postgresql-8.3-postgis/lwpostgis.sql:1563: ERROR:  current 
transaction is aborted, commands ignored until 

This error repeats all the way up to 6430 until the command "ROLLBACK" is 
issued. 

How I can diagnose the source of this error as its been plaguing me on 8.2 as 
well as 8.3. System is latest Kubuntu with all updates and the user chris123 
has access to postgresql. 

Kind regards for any insight or info. 


Best and thanks

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

[postgis-users] Visualizing Attribute Data

[Lee Hachadoorian]

In April there was a discussion GIS clients used for working with
PostGIS, with a lot of support for OpenJump, QGIS, and uDig.

I'm curious to hear people's strategies for visualizing attribute
data.  I'm doing some spatial querying, but most of my GIS data is
stable.  I've got census tracts, they don't change, but I need to grab
different combinations of census data and calculated data.  Some ways
I can think of to do it include:

1) Views to link spatial data to attribute data. (I then add a row to
geometry_columns with the view name.)
2) Materialized view, creating a new table with the desired spatial
and attribute data.  Main worry is the materialized view getting out
of sync if I change the attribute data.
3) Export to shapefile.

Any opinions, strategies, ideas about the advantages or disadvantages
of these strategies?  I'm using ArcGIS with zigGIS, but also playing
around with QGIS and uDig, all of which can work with PostGIS layers
or shapefiles.

Thanks,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] create tables smarter

[Lee Hachadoorian]

Kevin,

In that case I think Andreas could accomplish this using

CREATE TABLE new_table (
LIKE old_table INCLUDING CONSTRAINTS
);

followed by probe_geometry_columns( ).  I tested this in my database
on an arbitrarily selected spatial table and it seems to have worked.

Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

On Wed, Jun 4, 2008 at 11:57 AM, Kevin Neufeld <[EMAIL PROTECTED]> wrote:
> Actually Lee, addgeometrycolumn does three things.  Two, as you mentioned,
> and three, it adds constraints to your table so that all geometries in your
> column have the same srid and are of the same dimension and geometry type.
>
> The function Stephen mentioned does exist, but it's called
> probe_geometry_columns().  Unfortunately, it's not in the docs.
>
> postgis=# \df *probe*
>List of functions
>  Schema |  Name  | Result data type | Argument data types
> ++--+-
>  public | probe_geometry_columns | text |
> (1 row)
>
> This function was written quite some time ago, and in my opinion, only
> partially works.  It adds missing entries to the geometry_columns table by
> scanning the system tables for the three above mentioned constraints.  In
> other words, if the three constraints don't live on your new geometry table,
> it won't add an entry to the geometry_columns table. Further, this function
> does not remove obsolete entries from the geometry_columns table.
>
> I wrote my own function that truncates and repopulates the geometry_columns
> table by scanning the system tables for columns of type 'geometry', rather
> than looking for existing constraints.  It tries to add the constraints to
> the table the geometry is in (sending you a warning if it failed), and
> inserts a row in the geometry_column table. I suppose one of these days, I
> should add it to the postgis source.
>
> Cheers,
> Kevin
>
>
> Lee Hachadoorian wrote:
>>
>> Andreas,
>>
>> AddGeometryColumn does two things: it adds a column of type geometry,
>> and it adds a row to table geometry_columns.  If I understand what it
>> is you want to do, you can do the SELECT and then add the row to
>> geometry_columns with an INSERT statement:
>>
>> INSERT INTO public.geometry_columns
>> VALUES
>> ('',,);
>>
>> where the arguments use the same values you would use for the
>> AddGeometryColumn function.
>>
>> Stephen, I don't see anything about a probegeometrycolumns( ) function
>> in the PostGIS docs.  Can you point me in the right direction?
>>
>> Thanks,
>> Lee Hachadoorian
>> PhD Student, Geography
>> Program in Earth & Environmental Sciences
>> CUNY Graduate Center
>> ___
>> 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] create tables smarter

[Lee Hachadoorian]

Andreas,

AddGeometryColumn does two things: it adds a column of type geometry,
and it adds a row to table geometry_columns.  If I understand what it
is you want to do, you can do the SELECT and then add the row to
geometry_columns with an INSERT statement:

INSERT INTO public.geometry_columns
VALUES ('',,);

where the arguments use the same values you would use for the
AddGeometryColumn function.

Stephen, I don't see anything about a probegeometrycolumns( ) function
in the PostGIS docs.  Can you point me in the right direction?

Thanks,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Importing shapefiles with srid != -1

[Lee Hachadoorian]

Is there any chance spatial_ref_sys is missing srid 4326?

Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

On Fri, May 9, 2008 at 12:21 PM, Stephen Lee <[EMAIL PROTECTED]> wrote:
> Hi James,
>
> Why not try qGIS and use its import wizard to give it a shot?
>
> regards,
>
> stephen
>
> On Fri, May 9, 2008 at 8:01 PM, James Dominy <[EMAIL PROTECTED]> wrote:
>>
>> Hi all,
>>
>> I have a shape file with an associated prj. The prj file says the shape
>> file is in WGS84, so I'm using
>>
>> shp2pgsql -a -g gisdata -s 4326 provinces.shp GISProvices
>>
>>
>>
>> When I run the result through psql, I get
>>
>> ERROR: new row for relation "gisprovices" violates check constraint
>> "enforce_srid_gisdata"
>>
>>
>>
>> The table definition is as follows...
>>
>> CREATE TABLE GISProvinces (
>>region_id SERIAL PRIMARY KEY,
>>name VARCHAR(255) NULL,
>>layer INTEGER NOT NULL REFERENCES TGISLayer
>> );
>> SELECT AddGeometryColumn('', 'gisprovinces', 'gisdata', 4326,
>> 'MULTIPOLYGON', 2);
>>
>> Everything works fine if I don't specify any srid. As soon as I specify an
>> SRID for either the column or the inserts (via -s with shp2pgsql), or both,
>> even if they are the same, I get the error. What am I doing wrong?
>>
>> Thanks,
>> James
>> ___
>> 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] Help

[Lee Hachadoorian]

Are you trying to do this in pgAdmin?  If you clicked on
template_postgis in the left pane server/database hierarchy, pgAdmin
is now using the database and can't also use it as a template for a
new DB.  (pgAdmin is using the database if the database icon does
*not* have a little red X over it.)

You should be able to fix this by disconnecting from the server, then
reconnecting.  Create your new DB before doing anything that uses the
template_postgis database.

Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

On Wed, May 7, 2008 at 9:25 AM, eehab hamzeh <[EMAIL PROTECTED]> wrote:
>
> Hello
>
>  I am a new user to PostGIS, i try to create a database with the following
>
>
>
>
> §Name der DB: ihab
> §Eigentümer der DB: postgres
>
> §Kodierung der Zeichen: LATIN1
>
> §Vorlage: template_postgis
>
> I received the following message
>
> 'Error: Source database 'Template_postgis' is being used by other user'
>
>
> thanks
>
> Ihab
>
>
> 
> Explore the seven wonders of the world Learn more!
> ___
>  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] Best graphical viewer for PostGIS data

[Lee Hachadoorian]

I'm new to the PostGIS world, so FWIW:

I tried QGIS 0.9.1.  Very easy PostGIS connection.  Main problems I
encountered are poor tools for controlling symbology and extremely
poor tools for page layout.  I notice that QGIS is participating in
the Google Summer of Code, so I will probably check it out again this
Fall, and continue to use QGIS 0.9.1 on my Linux machine.

On my Windows machine, I've been using ArcGIS Desktop 9.2 with the
zigGIS PostGIS connector available at
http://code.google.com/p/ziggis/.  I found table joins in ArcGIS to be
unbelievably slow.  I had better results creating the desired layer as
a Postgres view, then using zigGIS to connect to the view.  (Slower
than using ESRI shapefile or geodatabase format, but tolerable.)  The
advantages and disadvantages of ArcGIS (proprietary, $$$) are probably
well-known.

Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Adding view to geometry_columns table

[Lee Hachadoorian]

Is there any problem, or is it common, to add a view to the
geometry_columns table?  I'm experimenting using zigGIS to add PostGIS
layers to an ArcGIS 9.2 project.  It seems that zigGIS only "sees"
tables listed in geometry_columns (unlike Quantum GIS which "sees"
anything with a geometry column, including views based on tables with
geometry columns).  So I manually added a row to geometry_columns with
the names of the schema, view (in column f_table_name) and geometry
column.  Seems to be working fine, and this way I can easily add or
drop attributes from the layer, or join data from several different
tables.  Are there are any pitfalls I should be aware of?

Thanks,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] SRID and unprojected (Census) data

[Lee Hachadoorian]

Regina, Leo, Chris,

I had previously installed from packages on Ubuntu Linux, which means 
Postgres 8.2 and PostGIS 1.2 (repositories aren't up to date).  I just 
decided to build everything from source (Postgres, Proj4, GEOS, and 
PostGIS), and SRID 4269 is in spatial_ref_sys.   I don't think it was 
there previously, but then again, I might have missed it.  I did


SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%NAD83';

but if 4269 was in the results, I didn't spot it.

So, if I understand correctly, I should run shp2pgsql with -s 4269 in 
order to import the shapefiles that I download from the census.


Thanks for your help,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] SRID and unprojected (Census) data

[Lee Hachadoorian]

Can someone please give me some ideas, or pointers to where this 
question is answered elsewhere, on how to handle unprojected geographic 
data in PostGIS?  Would I just create the table with SRID = -1?  The US 
Census makes several cartographic boundary files available using the 
NAD83 datum, but unprojected (with just lat/long coordinates for each 
point or polygon).  I looked through spatial_ref_sys, and there are over 
500 NAD83 reference systems, but it appears that they are all projected 
(UTM, state plane, whatever).


Thanks in advance,
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Turn a Polygon into Lines

[Lee Hachadoorian]

Thanks for the responses.  Sounds somewhat complicated, and I'm just 
getting started with PostGIS after a few years using ESRI products.  If 
PostGIS doesn't support topology, I'm going to have to get more 
experience with it before trying to roll my own.But when I'm feeling 
like experimenting I'll look back over what you said.


Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Turn a Polygon into Lines

[Lee Hachadoorian]

I'm looking through the PostGIS reference, and I can't seem to find a 
way to take a geometry of polygons and turn it into lines.  What I'm 
looking for is something like the ArcGIS Feature to Line geoprocessor, 
which will create a line shapefile where each feature is an arc 
representing the boundary between neighboring polygons with a field 
indicating the ids of the polygon on either side.


Functions like ST_MakeLine require point geometries, and I don't see 
anything else that seems to be what I'm looking for.  Any ideas would be 
welcome.


Thanks,
Lee Hachadoorian
PhD Student in Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users