If by a pop-up saying PostgreSQL Tools has stopped RESPONDING, it may be the
shear size of your file. On windows, I have observed such a phenomenon when
trying to import fairly large postgresql files (the size of yours ranks up
there as fairly large).
To verify if this was the cause, excerpt
I am importing a file from some colleagues that is throwing an error
that I have never encountered before (nor it seems, has Google).What
is interesting is that this file was an update sent to me whose only
supposed difference is that it is a POINT shapefile instead of a
MULTIPOLYGON. The
...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Burgholzer, Robert (DEQ)
Sent: Friday, April 01, 2011 4:31 PM
To: postgis-us...@refractions.net
Subject: [postgis-users] shp2pgsql import error: invalid byte sequence
forencoding UTF8: 0x96
I am importing a file from
in bad plans for queries like ... from a left join b on
a.a1 = b.b1 where a.a1 = 42 ...
Hope that helps,
Regina
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Wednesday
-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Wednesday, March 18, 2009 3:27 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Index Use Craziness
Regina,
Thanks, I am using 8.2, and you may be right. I need
I am having much trouble getting postgres to use the proper index.
I have a table with about 20 million daily rainfall observation points,
with between 0 and 19,000 points on any given day. In order to speed up
querying, I have created a GIST index on the geometry column, and an
index on the
used this inherited tables idea to
partition by months and a query for rainfall 15 years ago is now the
same as within the last month. Before it was several orders of
magnitude slower.
Bruce
Burgholzer,Robert wrote:
I am having much trouble getting postgres to use the proper index.
I have
...@postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Wednesday, March 18, 2009 9:05 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Index Use Craziness
I am having much trouble getting postgres to use the proper index.
I have a table with about 20 million daily rainfall observation
Youssef,
Ben mentioned the create lang step. I suspect this is what you are missing.
The postgis functions are written in the language pl/Pgsql, which is an
iptional add-on for any postgresql database. In order to add this language,
you must do the following:
sudo su - postgres -c
FYI,
Depending on where you are located, the NOAA has rainfall at 4km x 4km
grids, and a host of wfs-queryable data at a dynamic grid size (that I
suspect may conform to the 4km x 4km grid as well).
I can dig up the URLs if you are interested.
Robert W. Burgholzer
Surface Water Modeler
Office of
This is an announcement of the official release of software for creating system
dynamics models using nothing but a web-browser. This software is constructed
of 100% Open Source software, and leverages such geospatial products as
PostGIS, UMN MapServer and OpenLayers. User accounts are free,
Mark,
This could be either a mapserver question, or a postgis question
depending on the solution you chose. I would say the following
(untested code):
Mapserver:
- define 3 layers, 1 for each concentric circle using your 1 dd
circle as the template, with duplicates of 2 and 3
I realize that it would only be a drop in the bucket, but would seeking Google
Summer of Code funds be a helpful avenue?
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling
So,
Do you need to iterate through the sub-polygons in your multi and get a
boundary of each?
r.b.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
I am just guessing, but why don't you try to install a fresh PostGIS
enabled db in the Ubuntu machine, perhaps something in the process of
restore was not compatible, and hosed your translation.
HTH,
r.b.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia
] On Behalf Of Burgholzer,Robert
Sent: Wednesday, November 12, 2008 12:53 PM
To: [EMAIL PROTECTED]; PostGIS Users Discussion
Subject: RE: [postgis-users] Problem in migrating shapefile to postgis
First place to start to find the proper SRID is look for a metadata file for
your shape. If you have
You rule Regina.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-Original Message-
From: [EMAIL
Dana,
You can also run it in the manner that you are, i.e., exporting the
output to a file. I am curious, what does the script output look like
if you run it without dumping it to a file, i.e.:
c:/shp2pgsql -s 26912 -p c:/Jordyn Jordyn
Whatever error message it dumps should be useful.
Robert
Dara,
I think that it might have a problem with your file path. As I think the
previous respondent was indicating, it may work OK if you use a file
name without directory prefixes.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of
Would the following work (if max_distance were available, which it is
not in my distro)??:
SELECT ST_Buffer(centroid(the_geom), max_distance(centroid(the_geom),
the_geom) as bounding_circle ;
This assumes the following:
1. The Roeck test is essentially the bounding circle of a geometry
2. The
I have seen some threads regarding how to create contour lines from a
set of points in PostGIS, but have not been able to locate any posts or
web pages that detail the process with either a function, or some SQL
code snippets.
Has any one come up with such a thing, and if so, where might I
This is interesting, so I am kind of thinking in email ...
Assumptions:
- a good goal would be to eliminate the least number of polygons to
create a non-overlapping set
- polygons which overlap multiple polygons would therefore be better
candidates, or at least
- Martin is probably right that
Regina,
If what you are tackling is something that does a cross-walk between a
Esri-geodatabase and PostGIS db, I am very interested in this topic. I am in a
mixed media shop, which means I use PostGIS and everyone else uses
MS/Esri-centric stuff, and this would really be a leg up for not only
meta tables when I add/update data.
Also how do you deal with the spatial data part using plain ODBC or do you not
need to for your use-case.
Thanks,
Regina
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Burgholzer,Robert
Sent: Friday, September 19
This works on my machine:
pgsql2shp db_name select * from proj_points where projectid = 2 limit
5 -f test_shp -P my_password -u dbuser
where the quote is in selects, and could easily be a PostGIS boundary
box, I think.
HTH,
r.b.
Robert W. Burgholzer
Surface Water Modeler
Office
http://www.sogis1.so.ch/sogis/dl/postgis/cleanGeometry.sql
you need caps on the G in geometry.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
Carmen,
The trouble here is not with PostGIS per se, but with the SQL statement
and what Qgis expects. When Qgis loads a table from postgres, it wants
there to be a column named OID.
You can do this by creating a view that includes an OID column -
although this is most easily done if your
of holding spatial data.
ie.
CREATE TEMP TABLE test (id integer, the_geom geometry);
\d
Table pg_temp_11.test
Column | Type | Modifiers
--+--+---
id | integer |
the_geom | geometry |
Cheers,
Kevin
Burgholzer,Robert wrote:
I have a temporary table that I
addgeometrycolumn at all. You just need to
create a table with a column declared as geometry.
Create table foo (my_geom_column geometry, my_id serial, my_attribute
varchar);
On Tue, Aug 26, 2008 at 8:20 AM, Burgholzer,Robert
[EMAIL PROTECTED] wrote:
Kevin,
Thanks for the response. By spatially enabled, I
-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of David William Bitner
Sent: Tuesday, August 26, 2008 1:29 PM
To: Burgholzer,Robert
Cc: PostGIS Users Discussion
Subject: Re: [postgis-users] schema name for a temp table?
Interoperability and standards. The geometry_columns table
Birgit,
My guess is that the part of the query that is causing the memory dump
is not the GIST index, but rather the within query, which must iterate
through all of your points.
That said, if you wish to determine if the GIST index is causing
problems, you could do the in a separate query, store
I have a temporary table that I would like to spatially enable. Thus, I
need to know the proper schema name, assuming that the schema name would
change if there were multiple temp tables with the same name (a
possibility if several users are hitting the app simultaneously).
Has anyone had any
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Burgholzer,Robert
Sent: Wednesday, August 06, 2008 3:08 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] filing the holes in the swiss cheese?
Thanks Paul, but this gives me no records at all.
BTW - the dirty
I am trying to do a query of cities that are contained by counties (in
Virginia, US), so that I can have a list that cross-references by FIPS
these relationships.
My query looks like this:
select b.poli1 as fips, b.name, a.poli1 as contained_by_fips, a.name
from poli_bounds as a ,
b.poli1
and st_contains(a.the_geom, st_pointonsurface(b.the_geom))
and a.projectid = 1
and b.projectid = 1;
that should rid you of the boundary conditions plaguing st_contains.
P.
ps - note the modern st_contains(), with implicit index call.
On Wed, Aug 6, 2008 at 8:19 AM, Burgholzer,Robert
[EMAIL
but it's
result violates the geometry check constraint.
On Fri, Aug 1, 2008 at 5:31 PM, danny [EMAIL PROTECTED] wrote:
Thanks Robert.
I actually exported from ogr2ogr and it created a shape that worked fine
in ArcView.
Best
On Fri, Aug 1, 2008 at 5:19 PM, Burgholzer,Robert
[EMAIL
Could ArcView perhaps want some columns other than just the shape?
Perhaps you could put a numeric ID or something else in there.
Just a thought,
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL
Ervin,
I tend to store in 4326, a lat/lon system, that seems to work OK as a
generic. I am not real strong in the way of projection and data
distortion, and for my data, I have thus far found no real trouble.
As for your update queries, that seems like it would work fine. The
other way that
Bob,
What Marin said was that since you chose to base the offset calculations
for each shape on the centroid of that particular shape relative to your
reference shape, all shapes would be moved to center on your reference
shape.
If you wish to preserve their positions relative to one another,
Marc,
Would the following work: box2d(transform( extent(wkb_geometry), 4326) )
I think that you will achieve an efficiency from the following points:
1. you should not need to collect, if all you want is the bounding
box, since extent() should give you that
2. Even if you DO
/
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Burgholzer,Robert
Sent: Monday, June 30, 2008 10:08 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Collecting points for the overall bounding
box
Marc,
Would the following work: box2d(transform( extent
ODBC connections will do the trick. If you have the PostgreSQL ODBC driver
(downloadable from the web somewhere) you can configure a data source on your
windows system, then use the Export function in MSAccess to export the tables
into your postgresql datasource.
Robert W. Burgholzer
You need to use a LEFT OUTER JOIN, like so (although I am only doing the
simple case of joining edges and addr, but you can probably extend
it to your full case):
SELECT a.tlid, c.fromhn, c.tohn, c.side,
c.zip, c.plus4, a.statefp, a.countyfp
FROM edges a LEFT OUTER JOIN addr AS c ON
Bob,
You need to post your table definition, and your update syntax in order to
diagnose the problem.
r.b.
-Original Message-
From: [EMAIL PROTECTED] on behalf of Bob Pawley
Sent: Thu 6/5/2008 12:20 PM
To: PostGIS Users Discussion
Cc:
Subject:Re: [postgis-users]
This from a postgres post on a similar topic from Tom Lane:
Rules are macros, which means that expansion has to terminate
statically, not dynamically. For the particular purpose you seem to
have here, it'd be a lot more manageable and a lot more efficient
to use a BEFORE UPDATE trigger
You're seeing binary. Some where along the way the storage of shapes in
PostGIS was changed to that. To see the type of stuff you want, AFTER
inserting your data, you can do:
SELECT gid, name, asText(the_geom) from geotable;
Robert W. Burgholzer
Surface Water Modeler
Office of Water
Bob,
In his example, Stanley's table expects the parent_entity_id column to
contain a reference to an entity_id value from some other object in
the entity table. Thus, if you are inserting a parent object, or a
parentless object, you put nothing, or NULL in that column (if you put
nothing, it
Marcus,
1) You could create your table prior to populating it, and include a column
that is type SERIAL, this will create an autogenerated integer identifier
that you could name gid if you so desired.
2) select area2d(Intersection(f.the_geom, c.the_geom)) ...
NOTE: You will have to convert this
insert into the x and y column.
But when I attempt to move the geometry that I want moved I get a
message
Argument to X() must be a point.
What am I doing wrong??
Bob
- Original Message -
From: Burgholzer,Robert [EMAIL PROTECTED]
To: PostGIS Users Discussion postgis-users
Bob,
I am taking this online, since it is relevant to PostGIS, and I want to make
sure that others review my comments for veracity
Original Question:
At the moment I am importing dxf files, representing process and devices,
into Postgis.
Say I want to make two processes A B.
I import
will register on the sourceforge and as soon as possible, ill upload my
data model.
I think this is a major step, since data models can simplify everyones life!
Thanks for answering Robert!
George
On Tue, May 27, 2008 at 9:45 AM, Burgholzer,Robert
[EMAIL PROTECTED] wrote:
George,
I went
Andy,
fix the original table:
update il_izmir3 set the_geom = ST_SetSRID(the_geom, 4326);
Unfortunately, no. As Regina pointed out, all this does is to change the SRID
number, it doesn't actually transform (reproject, that is) the geometry.
SetSRID is usually used when importing from
. That was the basis for my
comment.
-- Andy
On May 16, 2008, at 1:46 PM, Burgholzer,Robert wrote:
Andy,
fix the original table:
update il_izmir3 set the_geom = ST_SetSRID(the_geom, 4326);
Unfortunately, no. As Regina pointed out, all this does is to
change the SRID number, it doesn't
of Burgholzer,Robert
Sent: Fri 5/16/2008 2:27 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] couldn't get the correct projected values
I see what you're saying, but if you used shp2pgsql to import your shapes, and
didn't specify a geometry, it sets the SRID = -1, and this is how
I am willing to speculate that this is an issue about your disk, not
Postgres (which does not actually do a delete, it simply marks as no
longer used rows that are updated).
From the looks of your query, it is an update on every single row in
your table. Since this does not rely on any query
Paul,
Nice example on that generic spatial ref system for calculating
distances and areas in the continental US. This reminds me of a thread
about this a week or two ago. It seems to me that the need exists (or
at lease it could be useful) for an area/distance functions that can
make some
Paul,
Agreed that there is very little way to prevent people from making
dangerous inferences about in appropriate topics, so consider the
following:
Maybe part of the advanced options, or a corollary of such a function
could be a report of just how far the envelope has been pushed, i.e., a
could end up running a cartesian distance
op on one geometry that is projected into UTM10 and another that is in
UTM9.
P.
On Thu, May 15, 2008 at 8:12 AM, Burgholzer,Robert
[EMAIL PROTECTED] wrote:
Paul,
Agreed that there is very little way to prevent people from making
dangerous inferences about
Bruce,
Your point is well taken, but I suppose that I misrepresented my real
interest. While it is nice to provide functions that will *just work*
for people who don't know what they are doing, this type of function is
also quite useful for people who are writing software, and looking for a
way
David,
This is very nice, I think this could also be expanded, with some math,
to do the extent case as well. You make the assumption that the UTM
zone of geometry a is a better fit than geometry b's zone, but I think
that is a reasonable starting point, though arbitrary.
Will diddle around
Syntax for command please ...
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-Original Message-
From: [EMAIL
Source Modeling Tools:
http://sourceforge.net/projects/npsource/
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Burgholzer,Robert
Sent: Wednesday, May 14, 2008 2:36 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Having problemas to convert SHP
Stephen,
This is a classic, and I think it must be in some FAQ some where. The
only way I have done it is to re-project using the transform function to
something whose units are in meters, since the 4326 SRID has distance
units in meters. Thus, something like the following:
SELECT
Certainly this is possible. I suppose the question is, how?
Off the top of my head I can think of a couple of ways. IN these
examples I will assume that the house table has an ID column called
address that is shared by each of the corresponding entries in the
persons table. :
1. Create a
Horst,
I have what seems to be an ugly solution to your difficulty, but I think it
works. I do a manipulation on the asText entities, stripping out the POLYGON
label, and merging them together to form a MULTIPOLYGON. While this is ugly,
it could form the basis of a simple function to achieve
/
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Burgholzer,Robert
Sent: Thursday, April 24, 2008 9:20 AM
To: PostGIS Users Discussion
Subject: RE: AW: Re: AW: RE: [postgis-users] Geometrycollection to Multipolygon
Horst,
I have what seems to be an ugly
I use PL/R (the R statistical language embedded in PostgreSQL) for
performing some data inputs to thematic mapping functions, such as
quantile distribution. A small library of scripts is available at:
http://sourceforge.net/project/showfiles.php?group_id=168647package_id=
258531
Robert W.
Just to make sure that all bases are covered, you need to be certain
that the geometry columns have indices and are vacuum/analyze'ed.
r.b.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Martin Davis
Sent: Thursday, January 24, 2008 8:13 PM
To: PostGIS
Make sure that you have spatial indices on the geom columns in each
table. If that is already the case, the most likely culprit is extremely
complex polygon shapes, which the containment algorithms are not suited
to do quickly (although it might be the fastest algorithm possible).
Suggestions that
://sourceforge.net/projects/npsource/
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Burgholzer,Robert
Sent: Monday, January 14, 2008 12:26 PM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: RE: [postgis-users] Pipeline Data Model
OK
PM
To: PostGIS Users Discussion
Cc:
Subject:Re: [postgis-users] Pipeline Data Model
Robert, this sounds great. I'll give it a try this weekend.
BTW - where are you located? I'm in Richmond myself.
-Abe
On Jan 11, 2008 1:05 PM, Burgholzer,Robert
[EMAIL PROTECTED] wrote:
Dane
We might think to do a little divide and conquer strategy on the database that
Abe has, perhaps recruiting a group of individuals to each sign up for a
handful of tables to convert to postgres, and then upload them all to a
sourceforge repository.
r.b.
-Original Message-
From:
world indeed.
-Abe
On Jan 14, 2008 7:47 AM, Burgholzer,Robert
[EMAIL PROTECTED] wrote:
We might think to do a little divide and conquer strategy on the database
that Abe has, perhaps recruiting a group of individuals to each sign up for a
handful of tables to convert to postgres, and then upload
Springmeyer
Sent: Thursday, January 10, 2008 6:47 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Pipeline Data Model
Robert and Abe,
On Jan 10, 2008, at 12:48 PM, Burgholzer,Robert wrote:
If you have set up an ODBC connection to your PostgreSQL database, the
tables can be loaded
If you have set up an ODBC connection to your PostgreSQL database, the
tables can be loaded into postgres from MSAccess by using Access's
Export function. If there are spatial columns in there, this will be
a difficulty, but the regular data will upload. Of course, any linkages
will have to be
I have experienced this same difficulty when using the simplify()
function. I can eliminate some of them by using a smaller tolerance,
although it seems that there is something in the isValid routine that
OKs records that have some problems. I have found that if I use only
the buffer(the_geom,0)
Emilio,
That is the default storage now, binary. If you want the text
representation, select AsTExt(calles_geom);
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
I have experienced an instance where using simplify, depending on the
threshold chose, creates a geometry collection, rather than a
multipolygon (which was my source shape type).
I am unsure of exactly what is happening, but I know that with my
particular shapes, a threshold of 0.0025 (dd)
Andreas,
I have used the following query with varying degrees of success (that is,
sometimes the memory requirements seem to be too much, and it takes forever to
complete). The resulting shape occasionally renders strangely in Qgis.
SELECT landuse_name, memgeomunion(the_geom)
GROUP BY
avgpar, FROM weather
eliminate the comma.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
Web-Based Water
I am experiencing an error that I have seen in the archives with the
title: ERROR: function 60821C60 returned NULL. Any help would be
appreciated.
It seems that in the previous posts, a null geometry (or rather too many
of them) was the cause. I, however, am experiencing it with NO NULL
Gregory,
I have been using within() to do the same, and have found many of the same
performance issues. There are a couple of points that I think are useful:
1) the queries are generally fast, especially when using a GIST index,
because it is a very effective index - nothing comparable exists
Jan,
I have always done a manual insert into the geometry_columns table. I don't
know if this is right, but it works. I have also had some versions of
mapserver that want an OID column, so you may want to include that in your view
if it is not already there.
Robert
-Original Message-
83 matches
Mail list logo
