[postgis-users] postgis-users Digest, Vol 217, Issue 19
Freddy Gonthier
freddy.gonthier at gmail.com
Thu Mar 26 01:56:19 PDT 2020
SELECT *
FROM (SELECT *,
row_number() OVER () AS NumLigne
FROM p_loc.ebird_sel_spt
) AS p_loc_ebird_sel_sptAvecNumLigne
WHERE p_loc_ebird_sel_sptAvecNumLigne.NumLigne = 3329432;
Le mer. 25 mars 2020 à 20:02, <postgis-users-request at lists.osgeo.org> a
écrit :
> Send postgis-users mailing list submissions to
> 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
>
> You can reach the person managing the list at
> 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. Re: postgis-users Digest, Vol 217, Issue 15 (john polo)
> 2. Re: postgis-users Digest, Vol 217, Issue 15 (john polo)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 24 Mar 2020 21:42:29 -0700
> From: john polo <jpolo at mail.usf.edu>
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] postgis-users Digest, Vol 217, Issue 15
> Message-ID: <601441d5-0063-6f85-3e02-b3b4189be381 at mail.usf.edu>
> Content-Type: text/plain; charset="utf-8"; Format="flowed"
>
> 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-0001.html
> >
>
> ------------------------------
>
> Message: 2
> Date: Wed, 25 Mar 2020 06:22:35 -0700
> From: john polo <jpolo at mail.usf.edu>
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] postgis-users Digest, Vol 217, Issue 15
> Message-ID: <00de2ca7-dd58-15ab-2516-3a43e46777d2 at mail.usf.edu>
> Content-Type: text/plain; charset="utf-8"; Format="flowed"
>
> 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-0001.html
> >
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> ------------------------------
>
> End of postgis-users Digest, Vol 217, Issue 19
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200326/6c945b9c/attachment.html>
More information about the postgis-users
mailing list