[postgis-users] SQL Crashes Server

[strk at refractions.net]

On Mon, Mar 28, 2005 at 02:40:20PM -0600, Simpson wrote:
> 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'?

"needs proc upgrade" means your installation is insane ;)
PostGIS functionalities are given by a shared library AND
sql schema. The two must be in sync, or bad things can happen.

You basically need to install a fresh postgis (including the lwpostgis.sql
file).

--strk;


> 
> 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users