[postgis-users] SQL Crashes Server

strk at refractions.net strk at refractions.net
Mon Mar 28 09:19:31 PST 2005


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



More information about the postgis-users mailing list