Re: [postgis-users] Unique Constraint on Spatial Point violated

[Sairam Krishnamurthy]

Thanks Brent. Going to try this. I will update the progress.


Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On Fri, Apr 22, 2011 at 4:08 PM,  wrote:

> It is likely to be the fastest waty to initially populate the table as a
> bulk insert. There is nothing stopping you adding rows later however you
> like. I have populated tables with 250,000,000+ rows like this.
>
> The advantage is that once the data is loaded without constraints, you can
> run queries on the data to identify rows with values that will breach the
> constraints, then address the now identified problems so the constraints can
> be imposed.
>
> The first step is to get the data loaded. You can do this with your trigger
> & no constraint if you prefer. Then diagnose/fix/add constraint.
>
> I'd get the data inserted first, then add the composite unique index on
> lat/long. Then create the point. Note that your trigger may try to generate
> the point before the insert is validated, so could show the point
> duplication error prior to identifying the duplicate lat/lon data.
>
> Cheers,
>
>   Brent Wood
>
>
> --- On *Sat, 4/23/11, Sairam Krishnamurthy * wrote:
>
>
> From: Sairam Krishnamurthy 
> Subject: Re: [postgis-users] Unique Constraint on Spatial Point violated
> To: pcr...@pcreso.com
> Cc: "PostGIS Users Discussion" ,
> kulkarni.adity...@gmail.com
> Date: Saturday, April 23, 2011, 7:44 AM
>
>
> Well ... That wont in my case because I will be adding rows to the table
> later. Also it requires additional time to update the table. The table will
> have millions of rows.
>
> But is it really different from the why I am doing it right now? Will it
> help in the unique constraint in any way ?
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>
> On Fri, Apr 22, 2011 at 2:35 PM, 
> http://mc/compose?to=pcr...@pcreso.com>
> > wrote:
>
> I'd try a different approach to loading your data into the table.
>
> Try loading your lat/lon values using copy. This will be fastest (specify
> the field delimiter char): eg: cat  | psql -d  -c "copy 
> from STDIN with delimiter '?';"
>
> Then add your geometry column to the table.
> select addgeometrycolumn(...);
>
> Then update the geometry column using makepoint
> update table set geom = setsrid(makepoint(lon,lat));
>
> Then try to create your unique indexes & work through any duplicates in the
> db records.
>
> It is also a good idea to have unique indices as required on natural keys,
> but have an integer primary key on the table. This is easy to do:
>
> alter table add column gid serial (or bigserial);
>
> then make gid the primary key.
>
> --- On *Sat, 4/23/11, Sairam Krishnamurthy 
> http://mc/compose?to=kmsram...@gmail.com>
> >* wrote:
>
>
> From: Sairam Krishnamurthy 
> http://mc/compose?to=kmsram...@gmail.com>
> >
> Subject: [postgis-users] Unique Constraint on Spatial Point violated
> To: 
> postgis-users@postgis.refractions.net
> Cc: "Aditya Kulkarni" 
> http://mc/compose?to=kulkarni.adity...@gmail.com>
> >
> Date: Saturday, April 23, 2011, 7:10 AM
>
> All,
>
> I am loading a table from a file. The file is really big and has
> millions of rows. Table structure is described below:
>
> lat : double precision (primary key)
> lon: double precision (primary key)
> spatialPoint: geometry (unique)
>
> The file has lines of lat and lon: (lat,lon)
>
> Since I am loading from a file I cannot load the spatialPoint
> directly. I have a trigger to call the following function BEFORE
> INSERT OR UPDATE:
>
> BEGIN
> NEW."spatialPoint" := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
> RETURN new;
> END
>
> When I load the table I get unique key constraint on the filed
> spatialPoint. This happens when I try to load the point
> "-3.751046|-51.359041". But when I grep for the point in the file only
> one row exists.
>
> I am not sure if this is a precision problem in calculting the spatial
> points from the lat.lon value.
>
> Can someone help me with this?
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Unique Constraint on Spatial Point violated

[pcreso]

It is likely to be the fastest waty to initially populate the table as a bulk 
insert. There is nothing stopping you adding rows later however you like. I 
have populated tables with 250,000,000+ rows like this.

The advantage is that once the data is loaded without constraints, you can run 
queries on the data to identify rows with values that will breach the 
constraints, then address the now identified problems so the constraints can be 
imposed.

The first step is to get the data loaded. You can do this with your trigger & 
no constraint if you prefer. Then diagnose/fix/add constraint.

I'd get the data inserted first, then add the composite unique index on 
lat/long. Then create the point. Note that your trigger may try to generate the 
point before the insert is validated, so could show the point duplication error 
prior to identifying the duplicate lat/lon data.

Cheers,

  Brent Wood

--- On Sat, 4/23/11, Sairam Krishnamurthy  wrote:

From: Sairam Krishnamurthy 
Subject: Re: [postgis-users] Unique Constraint on Spatial Point violated
To: pcr...@pcreso.com
Cc: "PostGIS Users Discussion" , 
kulkarni.adity...@gmail.com
Date: Saturday, April 23, 2011, 7:44 AM

Well ... That wont in my case because I will be adding rows to the table later. 
Also it requires additional time to update the table. The table will have 
millions of rows. 


But is it really different from the why I am doing it right now? Will it help 
in the unique constraint in any way ?


Thanks,
Sairam Krishnamurthy
+1 612 859 8161



On Fri, Apr 22, 2011 at 2:35 PM,   wrote:


I'd try a different approach to loading your data into the table.

Try loading your lat/lon values using copy. This will be fastest (specify the 
field delimiter char): eg: cat  | psql -d  -c "copy  from 
STDIN with delimiter '?';"



Then add your geometry column to the table.
select addgeometrycolumn(...);

Then update the geometry column using makepoint
update table set geom = setsrid(makepoint(lon,lat));

Then try to create your unique indexes & work through any duplicates in the db 
records.



It is also a good idea to have unique indices as required on natural keys, but 
have an integer primary key on the table. This is easy to do:

alter table add column gid serial (or bigserial);

then make gid the primary key.



--- On Sat, 4/23/11, Sairam Krishnamurthy
  wrote:

From: Sairam Krishnamurthy 


Subject: [postgis-users] Unique Constraint on Spatial Point violated
To: postgis-users@postgis.refractions.net
Cc: "Aditya Kulkarni" 


Date: Saturday, April 23, 2011, 7:10 AM

All,

I am loading a table from a file. The file is really big and has
millions of rows. Table structure is described below:



lat : double precision (primary key)
lon: double precision (primary key)
spatialPoint: geometry (unique)

The file has lines of lat and lon: (lat,lon)

Since I am loading from a file I cannot load the spatialPoint


directly. I have a trigger to call the following function BEFORE
INSERT OR UPDATE:

BEGIN
    NEW."spatialPoint"
 := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
    RETURN new;
END

When I load the table I get unique key constraint on the filed
spatialPoint. This happens when I try to load the point
"-3.751046|-51.359041". But when I grep for the point in the file only


one row exists.

I am not sure if this is a precision problem in calculting the spatial
points from the lat.lon value.

Can someone help me with this?

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


___
postgis-users mailing list
postgis-users@postgis.refractions.net


http://postgis.refractions.net/mailman/listinfo/postgis-users




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Unique Constraint on Spatial Point violated

[Sairam Krishnamurthy]

Well ... That wont in my case because I will be adding rows to the table
later. Also it requires additional time to update the table. The table will
have millions of rows.

But is it really different from the why I am doing it right now? Will it
help in the unique constraint in any way ?

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On Fri, Apr 22, 2011 at 2:35 PM,  wrote:

> I'd try a different approach to loading your data into the table.
>
> Try loading your lat/lon values using copy. This will be fastest (specify
> the field delimiter char): eg: cat  | psql -d  -c "copy 
> from STDIN with delimiter '?';"
>
> Then add your geometry column to the table.
> select addgeometrycolumn(...);
>
> Then update the geometry column using makepoint
> update table set geom = setsrid(makepoint(lon,lat));
>
> Then try to create your unique indexes & work through any duplicates in the
> db records.
>
> It is also a good idea to have unique indices as required on natural keys,
> but have an integer primary key on the table. This is easy to do:
>
> alter table add column gid serial (or bigserial);
>
> then make gid the primary key.
>
> --- On *Sat, 4/23/11, Sairam Krishnamurthy * wrote:
>
>
> From: Sairam Krishnamurthy 
> Subject: [postgis-users] Unique Constraint on Spatial Point violated
> To: postgis-users@postgis.refractions.net
> Cc: "Aditya Kulkarni" 
> Date: Saturday, April 23, 2011, 7:10 AM
>
> All,
>
> I am loading a table from a file. The file is really big and has
> millions of rows. Table structure is described below:
>
> lat : double precision (primary key)
> lon: double precision (primary key)
> spatialPoint: geometry (unique)
>
> The file has lines of lat and lon: (lat,lon)
>
> Since I am loading from a file I cannot load the spatialPoint
> directly. I have a trigger to call the following function BEFORE
> INSERT OR UPDATE:
>
> BEGIN
> NEW."spatialPoint" := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
> RETURN new;
> END
>
> When I load the table I get unique key constraint on the filed
> spatialPoint. This happens when I try to load the point
> "-3.751046|-51.359041". But when I grep for the point in the file only
> one row exists.
>
> I am not sure if this is a precision problem in calculting the spatial
> points from the lat.lon value.
>
> Can someone help me with this?
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Unique Constraint on Spatial Point violated

[pcreso]

I'd try a different approach to loading your data into the table.

Try loading your lat/lon values using copy. This will be fastest (specify the 
field delimiter char): eg: cat  | psql -d  -c "copy  from 
STDIN with delimiter '?';"

Then add your geometry column to the table.
select addgeometrycolumn(...);

Then update the geometry column using makepoint
update table set geom = setsrid(makepoint(lon,lat));

Then try to create your unique indexes & work through any duplicates in the db 
records.

It is also a good idea to have unique indices as required on natural keys, but 
have an integer primary key on the table. This is easy to do:

alter table add column gid serial (or bigserial);

then make gid the primary key.

--- On Sat, 4/23/11, Sairam Krishnamurthy  wrote:

From: Sairam Krishnamurthy 
Subject: [postgis-users] Unique Constraint on Spatial Point violated
To: postgis-users@postgis.refractions.net
Cc: "Aditya Kulkarni" 
Date: Saturday, April 23, 2011, 7:10 AM

All,

I am loading a table from a file. The file is really big and has
millions of rows. Table structure is described below:

lat : double precision (primary key)
lon: double precision (primary key)
spatialPoint: geometry (unique)

The file has lines of lat and lon: (lat,lon)

Since I am loading from a file I cannot load the spatialPoint
directly. I have a trigger to call the following function BEFORE
INSERT OR UPDATE:

BEGIN
    NEW."spatialPoint" := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
    RETURN new;
END

When I load the table I get unique key constraint on the filed
spatialPoint. This happens when I try to load the point
"-3.751046|-51.359041". But when I grep for the point in the file only
one row exists.

I am not sure if this is a precision problem in calculting the spatial
points from the lat.lon value.

Can someone help me with this?

Thanks,
Sairam Krishnamurthy
+1 612 859 8161
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Unique Constraint on Spatial Point violated

[Sairam Krishnamurthy]

All,

I am loading a table from a file. The file is really big and has
millions of rows. Table structure is described below:

lat : double precision (primary key)
lon: double precision (primary key)
spatialPoint: geometry (unique)

The file has lines of lat and lon: (lat,lon)

Since I am loading from a file I cannot load the spatialPoint
directly. I have a trigger to call the following function BEFORE
INSERT OR UPDATE:

BEGIN
NEW."spatialPoint" := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
RETURN new;
END

When I load the table I get unique key constraint on the filed
spatialPoint. This happens when I try to load the point
"-3.751046|-51.359041". But when I grep for the point in the file only
one row exists.

I am not sure if this is a precision problem in calculting the spatial
points from the lat.lon value.

Can someone help me with this?

Thanks,
Sairam Krishnamurthy
+1 612 859 8161
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] [postgis-devel] PSC Vote to officially drop support for PostgreSQL 8.3 in PostGIS 2.0

[Magnus Hagander]

On Fri, Apr 22, 2011 at 02:57, Paragon Corporation  wrote:
>
> Mark,
> Agree with Paul -- we did say all PSC should at least feel comfortable with
> our position and be able to defend it.  Though probably something we need to
> clarify in our voting rules.
>
> I've cc'd the regular users group since I feel they would be most affected
> by this decision and would like to hear their opinions on it.
>
> First let's keep things in perspective.  We are talking about not having
> support for PostgreSQL 8.3 for PostGIS 2.0.  We will still do our duty and
> support PostgreSQL 8.3 on PostGIS 1.3-1.5 and if we don't have to worry
> about also supporting it on 2.0, we'll have many more cycles to support
> issues that arise in 1.3-1.5.
>
>
> More food for thought -
> From all the signals I have seen, I just feel trying to support PostgreSQL
> 8.3 on PostGIS 2.0 is a really bad idea.


Something else to put this in perspective: once people start putting
PostGIS 2.0 in production, PostgreSQL 8.3 will be down to just over a
year of life, maybe a year and a half. That's not going to be a very
future-proof combination *regardless*.

And I don't believe any of the major long-term distributions were on
8.3 anyway - 8.1 for the old redhat and 8.4 for the new one, for
example...

So I doubt it's worth the cost to keep supporting 8.3. *Particularly*
if it limits the functionality of PostGIS to do so.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] ST_Line_Substring, 2nd arg must be smaller then 3rd arg

[Julian Perelli]

Hello postgis list!

I have a large query, it was going good, but I wanted to add a
restriction to a path, this my WHERE clause:
WHERE
ST_Distance_Sphere(ST_GeomFromText('POINT($lat1 
$lng1)'),
ST_Line_Substring(re.path, 0, 0.5)) < $rad1
and
ST_Distance_Sphere(ST_GeomFromText('POINT($lat2 
$lng2)'),
ST_Line_Substring(re.path, 0, 0.5)) < $rad2
and

ST_Line_Locate_Point(ST_Line_Substring(re.path, 0, 0.5),
'POINT($lat1 $lng1)')
<

ST_Line_Locate_Point(ST_Line_Substring(re.path, 0, 0.5),
'POINT($lat2 $lng2)')

the $vars get replaced with good values.
When I don't use the ST_Line_Substring, I don't get an error, but when
I use it, I get "ERROR: 2nd arg must be smaller then 3rd arg". I
assume that someway in someplace something is not understanding that 0
is smaller than 0.5, but I really don't see the error in anyway.

Has anyone an indea of what is going on?

Thanks!! :D
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] [postgis-devel] PSC Vote to officially drop support for PostgreSQL 8.3 in PostGIS 2.0

[Ben Madin]

another 2c (AUS) worth - but that's 2.1 US cents at the moment!

Another strictly user, but I'll go along with Brent's sentiment on this - we 
are lucky that our servers are all FreeBSD - meaning that we are now running 
the 9.0.3 / 1.5.2 combination straight out of ports. Upgrades (just went from 
8.3 to 9.0) have gone smoothly. I wouldn't say we are early adopters, but our 
upgrade decisions are now driven by reducing providing backward 
compatibility... so I would be comfortable with the suggestion of not trying to 
provide backward compatibility to everyone.

I would however suggest that although I understand Regina's limit of 3, I think 
maintaining 8.4 support may have to exist beyond the release of PG9.2, as the 
move from 8.4 -> 9 is probably far more difficult that 8.3 -> 8.4.

cheers

Ben


On 22/04/2011, at 10:59 AM, Stephen Woodbridge wrote:

> Brent,
> 
> Well said! I was trying to frame this same sediment.
> 
> Devs,
> 
> I'm on the mapserver PSC and while I am a strong advocate for user issues and 
> release compatibility, I will be one of the first to say if a major release 
> is making things faster, better, decreasing maintenance at the cost of 
> breaking backwards compatibility, then we should do that. The incentive for 
> users to upgrade is based on there being lots of better, faster, quality 
> features that they do not have on the old releases.
> 
> Given what I have heard so far, I have old versions I can use if I have to, 
> and there seems to be lots of goodness to offset the pain of upgrading. So 
> 2.0 is the time to do this. Waiting until 3.0 will probably not a good idea.
> 
> Thanks for everyone time and efforts on building such a great product!
> 
> -Steve
> 
> On 4/21/2011 9:35 PM, pcr...@pcreso.com wrote:
>> 02c worth strictly from a user's perspective:
>> 
>> New users will generally start with current latest versions. So they
>> should be fine.
>> 
>> Old users who have difficulty upgrading. (Oft times me :-) my call. If I
>> need Postgis to work with 8.3, I use v1.5, if I need later Postgis
>> functionailty, I upgrade. I still have a choice, as long as the older
>> versions are available, even if they are no longer officially supported.
>> 
>> The rate of development of Postgis & Postgres is great. I'd sooner see
>> the developers free to develop, making the most of their valuable &
>> appreciated time, rather than spending time just keeping older Postgres
>> versions supported.
>> 
>> Thanks everyone!
>> 
>> Brent Wood
>> 
>> --- On *Fri, 4/22/11, Paragon Corporation //* wrote:
>> 
>> 
>>From: Paragon Corporation 
>>Subject: Re: [postgis-users] [postgis-devel] PSC Vote to officially
>>drop support for PostgreSQL 8.3 in PostGIS 2.0
>>To: "'PostGIS Development Discussion'"
>>
>>Cc: "'PostGIS Users Discussion'" 
>>Date: Friday, April 22, 2011, 12:57 PM
>> 
>> 
>>Mark,
>>Agree with Paul -- we did say all PSC should at least feel
>>comfortable with
>>our position and be able to defend it. Though probably something we
>>need to
>>clarify in our voting rules.
>> 
>>I've cc'd the regular users group since I feel they would be most
>>affected
>>by this decision and would like to hear their opinions on it.
>> 
>>First let's keep things in perspective. We are talking about not having
>>support for PostgreSQL 8.3 for PostGIS 2.0. We will still do our
>>duty and
>>support PostgreSQL 8.3 on PostGIS 1.3-1.5 and if we don't have to worry
>>about also supporting it on 2.0, we'll have many more cycles to support
>>issues that arise in 1.3-1.5.
>> 
>> 
>>More food for thought -
>> From all the signals I have seen, I just feel trying to support
>>PostgreSQL
>>8.3 on PostGIS 2.0 is a really bad idea.
>> 
>>I will add this. It's not just the testing, it’s the fact that requiring
>>our 2.0 code work on PostgreSQL 8.3 is going to slow our release as all
>>PostGIS developers will need to limit their feature set to work on
>>8.3 and
>>avoid new features that will make programming easier and more
>>efficient. We
>>have much more plpgsql code in PostGIS 2.0, than we have ever had in
>>prior
>>versions, which makes the task much more difficult.
>> 
>> From what I can gather most distros package just one version of
>>PostGIS with
>>each version of PostgreSQL if they package PostGIS at all. I just
>>helped a
>>client port their database to an ubuntu server on a different host
>>and the
>>stable on Ubuntu 10 is 8.4 with PostGIS 1.4. In fact even the backports
>>that have PostgreSQL 9.0, I can't find 1.5 so had to compile
>>ourselves to
>>get 1.5. This is not something most users new to PostgreSQL or
>>PostGIS will
>>be willing to do. So the reality is if they want to stay stable
>>they'll be
>>using 8.2 with 1.4. Similar story with centos. Yum rpms packages
>>just one
>>version of PostGIS with 8.4 a

Re: [postgis-users] PSC Vote to officially drop support for PostgreSQL 8.3 in PostGIS 2.0

[aperi2007]

Hi,

I like give my 2ct about this question.

As readeble to this link:
http://momjian.us/main/blogs/pgblog/2011.html#April_21_2011

Between pg 8.3 and pg 8.4 there is 20% code increasing
It is the 2nd biggest increase of code compare to all other version.

Even the PG9.1 platform has less increase of code.
(This is a surprise for me because I see many interesting news in PG 9.0 
/ PG9.1)


the difference between 9.1 and 8.4 is 130.000 code line
meanwhile between 8.4 and 8.3 are 170.000

Andrea.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users