Re: [postgis-users] postgis-users Digest, Vol 217, Issue 15
Freddy Gonthier
FROM
(SELECT *,
row_number() OVER () AS NumLigne
FROM TaTable
) AS TaTableAvecNumLigne
WHERE NumLigne = 3329432;
Send postgis-users mailing list submissions to
postgi...@lists.osgeo.org
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.osgeo.org/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
postgis-us...@lists.osgeo.org
You can reach the person managing the list at
postgis-u...@lists.osgeo.org
When replying, please edit your Subject line so it is more specific
than "Re: Contents of postgis-users digest..."
Today's Topics:
1. query a certain row from a table. (john polo)
2. Re: query a certain row from a table. (Alexander Gataric)
3. Re: query a certain row from a table. (John Polo)
----------------------------------------------------------------------
Message: 1
Date: Fri, 20 Mar 2020 21:35:21 -0700
From: john polo <jp...@mail.usf.edu>
To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
Subject: [postgis-users] query a certain row from a table.
Message-ID: <d05371ca-fcf0-d3e7...@mail.usf.edu>
Content-Type: text/plain; charset=utf-8; format=flowed
Hi,
I'm trying to export a table of points to a shapefile. The table has
over 8,000,000 rows. I'm using PostGIS 3.0 and PostGIS Shapefile
Import/Export Manager on Windows 10. When I try to run the export, I
keep getting this error:
Error: record 3329432 could not be created
I'm not sure how to handle this. My first impulse is find the offending
row, but I don't know the SQL for such a query. Is there something I
need to write in a WHERE clause or a different clause to get a specific row?
--
Enlightenment is ego's ultimate disappointment.
-Chogyam Trungpa
------------------------------
Message: 2
Date: Sat, 21 Mar 2020 02:36:24 -0500
From: Alexander Gataric <gat...@usa.net>
To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
Subject: Re: [postgis-users] query a certain row from a table.
Message-ID: <5c4820aa-f60d-4b7e...@usa.net>
Content-Type: text/plain; charset="utf-8"
What format are the points stored in? Point, multipoint? How many points per row?
Get BlueMail for Android
On Mar 20, 2020, 11:36 PM, at 11:36 PM, john polo <jp...@mail.usf.edu> wrote:
>Hi,
>
>I'm trying to export a table of points to a shapefile. The table has
>over 8,000,000 rows. I'm using PostGIS 3.0 and PostGIS Shapefile
>Import/Export Manager on Windows 10. When I try to run the export, I
>keep getting this error:
>
>Error: record 3329432 could not be created
>
>I'm not sure how to handle this. My first impulse is find the offending
>
>row, but I don't know the SQL for such a query. Is there something I
>need to write in a WHERE clause or a different clause to get a specific
>row?
>
>--
>Enlightenment is ego's ultimate disappointment.
>-Chogyam Trungpa
>
>_______________________________________________
>postgis-users mailing list
>postgi...@lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200321/b1b44f20/attachment-0001.html>
------------------------------
Message: 3
Date: Sat, 21 Mar 2020 08:06:34 -0700
From: John Polo <jp...@mail.usf.edu>
To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
Subject: Re: [postgis-users] query a certain row from a table.
Message-ID:
<CALTSVX9DsHew5rm2kbxjTQjD...@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
They are stored as Point. One per row.
On Saturday, March 21, 2020, Alexander Gataric <gat...@usa.net> wrote:
> What format are the points stored in? Point, multipoint? How many points
> per row?
>
> Get BlueMail for Android <http://www.bluemail.me/r?b=15824>
> On Mar 20, 2020, at 11:36 PM, john polo <jp...@mail.usf.edu> wrote:
>>
>> Hi,
>>
>> I'm trying to export a table of points to a shapefile. The table has
>> over 8,000,000 rows. I'm using PostGIS 3.0 and PostGIS Shapefile
>> Import/Export Manager on Windows 10. When I try to run the export, I
>> keep getting this error:
>>
>> Error: record 3329432 could not be created
>>
>> I'm not sure how to handle this. My first impulse is find the offending
>> row, but I don't know the SQL for such a query. Is there something I
>> need to write in a WHERE clause or a different clause to get a specific row?
>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200321/3c2986ce/attachment-0001.html>
------------------------------
Subject: Digest Footer
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------
End of postgis-users Digest, Vol 217, Issue 15
**********************************************
john polo
Thanks for the code, Freddy. It doesn't work for me though. I'm
still pretty new to SQL and subqueries are something I'm still
learning, so I don't know if there is something obvious I missed
in running this. I've not seen row_number() before. I read a
couple of things online and there was something that said the
query needs ORDER BY inside the OVER() function. However, I don't
know what to refer to for an ORDER BY(), because I don't know how
the table determines the line numbers.
SELECT *
FROM p_loc.ebird_sel_spt
(SELECT *,
row_number() OVER () AS NumLigne
FROM p_loc.ebird_sel_spt
) AS p_loc.ebird_sel_sptAvecNumLigne
WHERE NumLigne = 3329432;
ERROR: syntax error at or near "SELECT"
LINE 3: (SELECT *,
^
SQL state: 42601
Character: 3
I tried the query without the "AS
p_loc.ebird_sel_sptAvecNumLigne". I also forgot to change the
TaTable that was in the 2nd FROM to p_loc.ebird_sel_spt at first.
A few other little tweaks didn't seem to make it work for me.
best,
John
john polo
I see my mistake. I shouldn't have added the table name between
the first FROM and the second SELECT.
I don't know what's wrong with that row. It looks normal. I
suppose the problem is as others have pointed out: exporting the
table to shapefile leads to a shapefile that exceeds a size limit
of which I was not aware.
Merci beaucoup, Freddy.
best regards,
John