[postgis-users] Super weird problem: ST_GeomFromText('POINT(-114.112534 50.895364)') works, ST_GeomFromText('POINT(-114.228869 51.152249)') fails -- why?

[Sandro Santilli]

Please file a ticket, and report the output of postgis_full_version()

--strk;

On Mon, Jul 02, 2012 at 01:10:55PM +0200, René Fournier wrote:
> If I try to insert a row containing particular coordinate, the query fails and the DB connection is lost. (By comparison, hundreds of inserts of other coordinates work fine.) Here's a straight copy-and-paste comparison from psql:
> 
> mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id;
>  id  
> -----
>  333
> (1 row)
> 
> INSERT 0 1
> mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id;
> The connection to the server was lost. Attempting reset: Failed.
> !> 
> 
> Here's the table definition:
> 
> CREATE TABLE public.addresses
> (id serial NOT NULL,
> account_id int NOT NULL,
> territory_id int NOT NULL,
> location GEOGRAPHY(POINT,4326),
> PRIMARY KEY (id));
> CREATE INDEX location ON addresses USING GIST (location);
> 
> Strange right? FWIW, the queries are being generated programmatically by a script, so the error is not caused by a typo, since hundreds of other inserts work. Also, I've done a little research, two interesting findings:
> 
> 1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the SUCCESSFUL inserts are LOWER than the coordinates of failed query.
> 
> 2. If I create the table without the index on location, the failed inserts suddenly work. So it seems the problem lies with the PostGIS updating the Index -- maybe it doesn't like the size of the values of the larger coordinates?
> 
> Anyway, if you have any ideas of what I can do to fix this, I would love to hear them. Thanks!
> 
> ...Rene