[postgis-users] SQL Crashes Server

Simpson asimpson at i-55.com
Mon Mar 28 12:40:20 PST 2005


Here is the output from postgis_full_version:

 POSTGIS="1.0.0RC4" GEOS="2.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004"
DBPROC="0.2.0" RELPROC="0.2.1" (needs proc upgrade)

What does '(needs proc upgrade)' mean?  Not sure what you mean by 'sane'?

The geometry is in a binary format, I have to use astext to see what's in
them.

If needed, I'd be glad to dump and zip the data.

Thanks,
Drew.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of
strk at refractions.net
Sent: Monday, March 28, 2005 11:20 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] SQL Crashes Server


Is your wkb_geometry an hexwkb representation ?
Is your postgis installation sane (check output of postgis_full_version) ?
Can you provide a full-encosed test file for us to check ?

--strk;

On Mon, Mar 28, 2005 at 11:14:50AM -0600, Simpson wrote:
>
> Greetings,
>
> I have loaded several states of road data from TigerLine 2004 and now
> spatial queries on the table are crashing the server:
>
>  select count(*) from roads WHERE wkb_geometry &&
> setSRID('BOX3D(-89.8236106236772 	30.0599242582496,-89.4893166693257
> 30.2718478483578)'::BOX3D, 4269 );
> 	server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> 	The connection to the server was lost. Attempting reset: Failed.
>
> I ran "vacuum analyze roads", but it didn't help.
>
> I can run other queries on the table without any trouble.
>
> I tried to search for not isvalid:
>
> tiger2004=# select count(*) from roads where not isvalid(wkb_geometry);
> 	server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> 	The connection to the server was lost. Attempting reset: Succeeded.
>
> It seems there is something in the geometry column of one of the rows
> causing spatial functions to crash the server.
>
> Does anyone have any suggestions on how to determine which row is having
> issues, or any information on how to fix this?
>
>
> Thanks for any info,
> Drew.
>
> =================================  VERSION INFO
> ===============================
> postgis version 1.0.0-rc4 from 3/18/05
> postgresql 8.0
> linux  2.4.18-14 #1 Wed Sep 4 12:13:11 EDT 2002 i686 athlon i386 GNU/Linux
>
> ================================== TABLE INFO
> =================================
>
> tiger2004=# \d roads
>                                    Table "public.roads"
>     Column    |     Type      |                         Modifiers
>
> --------------+---------------+-------------------------------------------
--
> ----
> -----------
>  ogc_fid      | integer       | not null default
> nextval('public.roads_ogc_fid_s
> eq'::text)
>  module       | character(8)  |
>  tlid         | numeric(10,0) |
>  side1        | numeric(1,0)  |
>  source       | character(1)  |
>  fedirp       | character(2)  |
>  fename       | character(30) |
>  fetype       | character(4)  |
>  fedirs       | character(2)  |
>  cfcc         | character(3)  |
>  fraddl       | character(11) |
>  toaddl       | character(11) |
>  fraddr       | character(11) |
>  toaddr       | character(11) |
>  friaddl      | character(1)  |
>  toiaddl      | character(1)  |
>  friaddr      | character(1)  |
>  toiaddr      | character(1)  |
>  zipl         | numeric(5,0)  |
>  zipr         | numeric(5,0)  |
>  aianhhfpl    | numeric(5,0)  |
>  aianhhfpr    | numeric(5,0)  |
>  aihhtlil     | character(1)  |
>  aihhtlir     | character(1)  |
>  census1      | character(1)  |
>  census2      | character(1)  |
>  statel       | numeric(2,0)  |
>  stater       | numeric(2,0)  |
>  countyl      | numeric(3,0)  |
>  countyr      | numeric(3,0)  |
>  cousubl      | numeric(5,0)  |
>  cousubr      | numeric(5,0)  |
>  submcdl      | numeric(5,0)  |
>  submcdr      | numeric(5,0)  |
>  placel       | numeric(5,0)  |
>  placer       | numeric(5,0)  |
>  tractl       | numeric(6,0)  |
>  tractr       | numeric(6,0)  |
>  blockl       | numeric(4,0)  |
>  blockr       | numeric(4,0)  |
>  wkb_geometry | geometry      |
> Indexes:
>     "roads_pkey" PRIMARY KEY, btree (ogc_fid)
>     "roads_oid_idx" btree (oid)
> Check constraints:
>     "enforce_geotype_wkb_geometry" CHECK (geometrytype(wkb_geometry) =
> 'LINESTRI
> NG'::text OR wkb_geometry IS NULL)
>     "enforce_dims_wkb_geometry" CHECK (ndims(wkb_geometry) = 2)
>     "enforce_srid_wkb_geometry" CHECK (srid(wkb_geometry) = 4269)
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





More information about the postgis-users mailing list