[postgis-users] no row selected on spatial query

[Ben Madin]

Puneet - I think this can happen if you load a shapefile that is projected into a table specifying a lat long srid. I don't think the definition restricts the range of units.

You will have to change the column definition I guess.

cheers

Ben


On 28/10/2011, at 8:06 AM, Puneet Kishor wrote:

> A brief follow-up on this --
> 
> On Oct 27, 2011, at 5:57 PM, Puneet Kishor wrote:
> 
>> I inherited two tables like so
>> 
>> 	db=# select ST_Extent(the_geom) from t1;
>> 	                    st_extent                    
>> 	-------------------------------------------------
>> 	 BOX(-180 5.67656603969958,180 89.9999951703269)
>> 	(1 row)
>> 
>> 	db=# select ST_Extent(the_geom) from t2;
>> 	                         st_extent                         
>> 	-----------------------------------------------------------
>> 	 BOX(-3666182.8927 1296299.9824,4440422.8742 8820425.3008)
>> 	(1 row)
>> 
>> I want to convert t2 to match t1. So, I tried the following --
>> 
>> 	db=# UPDATE t2 SET the_geom = ST_Transform(the_geom, 4326);
>> 	UPDATE 1354
>> 
>> 
>> But that didn't do it.
>> 
>> 	db=# select ST_Extent(the_geom) from t2;
>> 	                         st_extent                         
>> 	-----------------------------------------------------------
>> 	 BOX(-3666182.8927 1296299.9824,4440422.8742 8820425.3008)
>> 	(1 row)
>> 
>> 
>> What do I do to transform t2 so its geometry matches t1?
>> 
> 
> Turns out t2 was loaded from a shapefile using shp2pgsql using SRID 4326. Its table definition is like so
> 
> CREATE TABLE t2
> (
> gid serial NOT NULL,
> objectid integer,
> type integer,
> the_geom geometry,
> CONSTRAINT t2_pkey PRIMARY KEY (gid ),
> CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
> CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
> CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
> )
> WITH (
> OIDS=FALSE
> );
> 
> So, in spite of the `enforce_srid_the_geom` CONSTRAINT which should restrict geometry to 4326, seems like meter values got inserted into the table. How is that possible?
> 
> And, in any case, how can I repair this?
> 
> --
> Puneet Kishor
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users