[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] postgis-users Digest, Vol 217, Issue 15
From: john polo <jpolo () mail ! usf ! edu>
Date: 2020-03-25 13:22:35
Message-ID: 00de2ca7-dd58-15ab-2516-3a43e46777d2 () mail ! usf ! edu
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@lists.osgeo.org
> > <mailto:postgis-users-request@lists.osgeo.org>> a écrit :
> >
> > Send postgis-users mailing list submissions to
> > postgis-users@lists.osgeo.org <mailto:postgis-users@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@lists.osgeo.org
> > <mailto:postgis-users-request@lists.osgeo.org>
> >
> > You can reach the person managing the list at
> > postgis-users-owner@lists.osgeo.org
> > <mailto:postgis-users-owner@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@mail.usf.edu <mailto:jpolo@mail.usf.edu>>
> > To: PostGIS Users Discussion <postgis-users@lists.osgeo.org
> > <mailto:postgis-users@lists.osgeo.org>>
> > Subject: [postgis-users] query a certain row from a table.
> > Message-ID: <d05371ca-fcf0-d3e7-c85f-42f144a9132a@mail.usf.edu
> > <mailto:d05371ca-fcf0-d3e7-c85f-42f144a9132a@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@usa.net <mailto:gataric@usa.net>>
> > To: PostGIS Users Discussion <postgis-users@lists.osgeo.org
> > <mailto:postgis-users@lists.osgeo.org>>
> > Subject: Re: [postgis-users] query a certain row from a table.
> > Message-ID: <5c4820aa-f60d-4b7e-9ca1-45da6a8d416a@usa.net
> > <mailto:5c4820aa-f60d-4b7e-9ca1-45da6a8d416a@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@mail.usf.edu <mailto:jpolo@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@lists.osgeo.org <mailto:postgis-users@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@mail.usf.edu <mailto:jpolo@mail.usf.edu>>
> > To: PostGIS Users Discussion <postgis-users@lists.osgeo.org
> > <mailto:postgis-users@lists.osgeo.org>>
> > Subject: Re: [postgis-users] query a certain row from a table.
> > Message-ID:
> >
> > <CALTSVX9DsHew5rm2kbxjTQjDYo11Y9b_xHpy8D7Pm+pSsCB-ww@mail.gmail.com
> > <mailto:CALTSVX9DsHew5rm2kbxjTQjDYo11Y9b_xHpy8D7Pm%2BpSsCB-ww@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@usa.net
> > <mailto:gataric@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@mail.usf.edu
> > <mailto:jpolo@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@lists.osgeo.org <mailto:postgis-users@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@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
[Attachment #5 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>I see my mistake. I shouldn't have added the table name between
the first FROM and the second SELECT. <br>
</p>
<p>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. <br>
</p>
<p>Merci beaucoup, Freddy. <br>
</p>
<p>best regards,</p>
<p>John<br>
</p>
<div class="moz-cite-prefix">On 3/24/2020 9:42 PM, john polo wrote:<br>
</div>
<blockquote type="cite"
cite="mid:601441d5-0063-6f85-3e02-b3b4189be381@mail.usf.edu">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<p>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. <br>
</p>
<p>SELECT *<br>
FROM p_loc.ebird_sel_spt<br>
(SELECT *,<br>
row_number() OVER () AS NumLigne<br>
FROM p_loc.ebird_sel_spt<br>
) AS p_loc.ebird_sel_sptAvecNumLigne<br>
WHERE NumLigne = 3329432; <br>
</p>
<p>ERROR: syntax error at or near "SELECT"<br>
LINE 3: (SELECT *,<br>
^<br>
SQL state: 42601<br>
Character: 3<br>
</p>
<p>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. <br>
</p>
<p>best,</p>
<p>John<br>
</p>
<p><br>
</p>
<div class="moz-cite-prefix">On 3/23/2020 9:56 AM, Freddy Gonthier
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CABDF1V0OaNFyB=yH1fC-mFrU7DRrjgNxV1LRYM3Rdv4wCZrpAQ@mail.gmail.com">
<meta http-equiv="content-type" content="text/html;
charset=UTF-8">
<div dir="ltr">SELECT *<br>
FROM<br>
(SELECT *,<br>
row_number() OVER () AS NumLigne<br>
FROM TaTable<br>
) AS TaTableAvecNumLigne<br>
WHERE NumLigne = 3329432;</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">Le sam. 21 mars 2020
à 20:01, <<a
href="mailto:postgis-users-request@lists.osgeo.org"
moz-do-not-send="true">postgis-users-request@lists.osgeo.org</a>>
a écrit :<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">Send postgis-users
mailing list submissions to<br>
<a href="mailto:postgis-users@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br> <br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" \
moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
or, via email, send a message with subject or body 'help' to<br>
<a
href="mailto:postgis-users-request@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users-request@lists.osgeo.org</a><br> <br>
You can reach the person managing the list at<br>
<a
href="mailto:postgis-users-owner@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users-owner@lists.osgeo.org</a><br> <br>
When replying, please edit your Subject line so it is more
specific<br>
than "Re: Contents of postgis-users digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. query a certain row from a table. (john polo)<br>
2. Re: query a certain row from a table. (Alexander
Gataric)<br>
3. Re: query a certain row from a table. (John Polo)<br>
<br>
<br>
----------------------------------------------------------------------<br>
<br>
Message: 1<br>
Date: Fri, 20 Mar 2020 21:35:21 -0700<br>
From: john polo <<a href="mailto:jpolo@mail.usf.edu"
target="_blank" moz-do-not-send="true">jpolo@mail.usf.edu</a>><br>
To: PostGIS Users Discussion <<a
href="mailto:postgis-users@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a>><br> Subject: \
[postgis-users] query a certain row from a table.<br> Message-ID: <<a
href="mailto:d05371ca-fcf0-d3e7-c85f-42f144a9132a@mail.usf.edu"
target="_blank" \
moz-do-not-send="true">d05371ca-fcf0-d3e7-c85f-42f144a9132a@mail.usf.edu</a>><br> \
Content-Type: text/plain; charset=utf-8; format=flowed<br> <br>
Hi,<br>
<br>
I'm trying to export a table of points to a shapefile. The
table has <br>
over 8,000,000 rows. I'm using PostGIS 3.0 and PostGIS
Shapefile <br>
Import/Export Manager on Windows 10. When I try to run the
export, I <br>
keep getting this error:<br>
<br>
Error: record 3329432 could not be created<br>
<br>
I'm not sure how to handle this. My first impulse is find
the offending <br>
row, but I don't know the SQL for such a query. Is there
something I <br>
need to write in a WHERE clause or a different clause to get
a specific row?<br>
<br>
-- <br>
Enlightenment is ego's ultimate disappointment.<br>
-Chogyam Trungpa<br>
<br>
<br>
<br>
------------------------------<br>
<br>
Message: 2<br>
Date: Sat, 21 Mar 2020 02:36:24 -0500<br>
From: Alexander Gataric <<a href="mailto:gataric@usa.net"
target="_blank" moz-do-not-send="true">gataric@usa.net</a>><br>
To: PostGIS Users Discussion <<a
href="mailto:postgis-users@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a>><br> Subject: Re: \
[postgis-users] query a certain row from a table.<br>
Message-ID: <<a
href="mailto:5c4820aa-f60d-4b7e-9ca1-45da6a8d416a@usa.net"
target="_blank" \
moz-do-not-send="true">5c4820aa-f60d-4b7e-9ca1-45da6a8d416a@usa.net</a>><br> \
Content-Type: text/plain; charset="utf-8"<br> <br>
What format are the points stored in? Point, multipoint? How
many points per row?<br>
<br>
Get BlueMail for Android <br>
<br>
On Mar 20, 2020, 11:36 PM, at 11:36 PM, john polo <<a
href="mailto:jpolo@mail.usf.edu" target="_blank"
moz-do-not-send="true">jpolo@mail.usf.edu</a>> wrote:<br>
>Hi,<br>
><br>
>I'm trying to export a table of points to a shapefile.
The table has <br>
>over 8,000,000 rows. I'm using PostGIS 3.0 and PostGIS
Shapefile <br>
>Import/Export Manager on Windows 10. When I try to run
the export, I <br>
>keep getting this error:<br>
><br>
>Error: record 3329432 could not be created<br>
><br>
>I'm not sure how to handle this. My first impulse is
find the offending<br>
><br>
>row, but I don't know the SQL for such a query. Is there
something I <br>
>need to write in a WHERE clause or a different clause to
get a specific<br>
>row?<br>
><br>
>-- <br>
>Enlightenment is ego's ultimate disappointment.<br>
>-Chogyam Trungpa<br>
><br>
>_______________________________________________<br>
>postgis-users mailing list<br>
><a href="mailto:postgis-users@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br> ><a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" \
moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a
href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20200321/b1b44f20/attachment-0001.html"
rel="noreferrer" target="_blank" \
moz-do-not-send="true">http://lists.osgeo.org/pipermail/postgis-users/attachments/20200321/b1b44f20/attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 3<br>
Date: Sat, 21 Mar 2020 08:06:34 -0700<br>
From: John Polo <<a href="mailto:jpolo@mail.usf.edu"
target="_blank" moz-do-not-send="true">jpolo@mail.usf.edu</a>><br>
To: PostGIS Users Discussion <<a
href="mailto:postgis-users@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a>><br> Subject: Re: \
[postgis-users] query a certain row from a table.<br>
Message-ID:<br>
<<a
href="mailto:CALTSVX9DsHew5rm2kbxjTQjDYo11Y9b_xHpy8D7Pm%2BpSsCB-ww@mail.gmail.com"
target="_blank" \
moz-do-not-send="true">CALTSVX9DsHew5rm2kbxjTQjDYo11Y9b_xHpy8D7Pm+pSsCB-ww@mail.gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
They are stored as Point. One per row.<br>
<br>
<br>
<br>
On Saturday, March 21, 2020, Alexander Gataric <<a
href="mailto:gataric@usa.net" target="_blank"
moz-do-not-send="true">gataric@usa.net</a>> wrote:<br>
<br>
> What format are the points stored in? Point,
multipoint? How many points<br>
> per row?<br>
><br>
> Get BlueMail for Android <<a
href="http://www.bluemail.me/r?b=15824" rel="noreferrer"
target="_blank" \
moz-do-not-send="true">http://www.bluemail.me/r?b=15824</a>><br> > On Mar 20, \
2020, at 11:36 PM, john polo <<a href="mailto:jpolo@mail.usf.edu" target="_blank"
moz-do-not-send="true">jpolo@mail.usf.edu</a>> wrote:<br>
>><br>
>> Hi,<br>
>><br>
>> I'm trying to export a table of points to a
shapefile. The table has<br>
>> over 8,000,000 rows. I'm using PostGIS 3.0 and
PostGIS Shapefile<br>
>> Import/Export Manager on Windows 10. When I try to
run the export, I<br>
>> keep getting this error:<br>
>><br>
>> Error: record 3329432 could not be created<br>
>><br>
>> I'm not sure how to handle this. My first impulse
is find the offending<br>
>> row, but I don't know the SQL for such a query. Is
there something I<br>
>> need to write in a WHERE clause or a different
clause to get a specific row?<br>
>><br>
>><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a
href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20200321/3c2986ce/attachment-0001.html"
rel="noreferrer" target="_blank" \
moz-do-not-send="true">http://lists.osgeo.org/pipermail/postgis-users/attachments/20200321/3c2986ce/attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Subject: Digest Footer<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org"
target="_blank" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br> <a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" \
moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br> \
<br>
------------------------------<br>
<br>
End of postgis-users Digest, Vol 217, Issue 15<br>
**********************************************<br>
</blockquote>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" \
wrap="">_______________________________________________ postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a> <a \
class="moz-txt-link-freetext" \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<pre class="moz-signature" cols="72">--
Enlightenment is ego's ultimate disappointment.
-Chogyam Trungpa</pre>
</blockquote>
<pre class="moz-signature" cols="72">--
Enlightenment is ego's ultimate disappointment.
-Chogyam Trungpa</pre>
</body>
</html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic