[postgis-users] postgis-users Digest, Vol 217, Issue 15

Wed Mar 25 06:22:35 PDT 2020

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

On 3/24/2020 9:42 PM, john polo wrote:
>
> 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
>
>
> On 3/23/2020 9:56 AM, Freddy Gonthier wrote:
>> SELECT *
>> FROM
>>   (SELECT *,
>>           row_number() OVER () AS NumLigne
>>      FROM TaTable
>>   ) AS TaTableAvecNumLigne
>> WHERE NumLigne = 3329432;
>>
>> Le sam. 21 mars 2020 à 20:01, <postgis-users-request at lists.osgeo.org 
>> <mailto:postgis-users-request at lists.osgeo.org>> a écrit :
>>
>>     Send postgis-users mailing list submissions to
>>     postgis-users at lists.osgeo.org <mailto:postgis-users at 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-users-request at lists.osgeo.org
>>     <mailto:postgis-users-request at lists.osgeo.org>
>>
>>     You can reach the person managing the list at
>>     postgis-users-owner at lists.osgeo.org
>>     <mailto:postgis-users-owner at 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 <jpolo at mail.usf.edu <mailto:jpolo at mail.usf.edu>>
>>     To: PostGIS Users Discussion <postgis-users at lists.osgeo.org
>>     <mailto:postgis-users at lists.osgeo.org>>
>>     Subject: [postgis-users] query a certain row from a table.
>>     Message-ID: <d05371ca-fcf0-d3e7-c85f-42f144a9132a at mail.usf.edu
>>     <mailto:d05371ca-fcf0-d3e7-c85f-42f144a9132a at 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 <gataric at usa.net <mailto:gataric at usa.net>>
>>     To: PostGIS Users Discussion <postgis-users at lists.osgeo.org
>>     <mailto:postgis-users at lists.osgeo.org>>
>>     Subject: Re: [postgis-users] query a certain row from a table.
>>     Message-ID: <5c4820aa-f60d-4b7e-9ca1-45da6a8d416a at usa.net
>>     <mailto:5c4820aa-f60d-4b7e-9ca1-45da6a8d416a at 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
>>     <jpolo at mail.usf.edu <mailto:jpolo at 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
>>     >postgis-users at lists.osgeo.org <mailto:postgis-users at 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 <jpolo at mail.usf.edu <mailto:jpolo at mail.usf.edu>>
>>     To: PostGIS Users Discussion <postgis-users at lists.osgeo.org
>>     <mailto:postgis-users at lists.osgeo.org>>
>>     Subject: Re: [postgis-users] query a certain row from a table.
>>     Message-ID:
>>            
>>     <CALTSVX9DsHew5rm2kbxjTQjDYo11Y9b_xHpy8D7Pm+pSsCB-ww at mail.gmail.com
>>     <mailto:CALTSVX9DsHew5rm2kbxjTQjDYo11Y9b_xHpy8D7Pm%2BpSsCB-ww at 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 <gataric at usa.net
>>     <mailto:gataric at 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 <jpolo at mail.usf.edu
>>     <mailto:jpolo at 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
>>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>>     https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>     ------------------------------
>>
>>     End of postgis-users Digest, Vol 217, Issue 15
>>     **********************************************
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
> -- 
> Enlightenment is ego's ultimate disappointment.
> -Chogyam Trungpa

-- 
Enlightenment is ego's ultimate disappointment.
-Chogyam Trungpa

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200325/ecfd1837/attachment.html>