[postgis-users] postgis-users Digest, Vol 217, Issue 15
john polo
jpolo at mail.usf.edu
Tue Mar 24 21:42:29 PDT 2020
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200324/60c6afe6/attachment.html>
More information about the postgis-users
mailing list