[R-sig-Geo] CSV with Geometry Column to SF object

Barry Rowlingson b@row||ng@on @end|ng |rom gm@||@com
Tue Oct 15 21:47:34 CEST 2019 

These strings are hexadecimal WKB geometries. You should be able to turn
them back into geometries:

 > s =
"0105000020D708000001000000010200000003000000009875DF60AC2D4100606296BDAC07410037DB98F1AC2D41001C5EFC79AA074180A23112E1AA2D41001E0E5F18A20741"

by constructing a WKB object:

> wkb = structure(list(s), class = "WKB")

and then:

 > st_as_sfc(wkb)
wkbType: 536870917
Error in CPL_read_wkb(x, EWKB, spatialite) :
  unsupported wkbType dim in switch

but that error looks bad...

ahaha, they are EWKB:

> st_as_sfc(wkb, EWKB=TRUE)
Geometry set for 1 feature
geometry type:  MULTILINESTRING
dimension:      XY
bbox:           xmin: 972144.5 ymin: 193603 xmax: 972408.8 ymax: 193943.7
epsg (SRID):    2263
proj4string:    +proj=lcc +lat_1=41.03333333333333 +lat_2=40.66666666666666
+lat_0=40.16666666666666 +lon_0=-74 +x_0=300000.0000000001 +y_0=0
+ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs
MULTILINESTRING ((972336.4 193943.7, 972408.8 1...


On Tue, Oct 15, 2019 at 8:40 PM argunaw . <argunaw using gmail.com> wrote:

> Here is a small sample of the dataset I'm working with- the strings are
> really long for geometry, so apologies for the messiness.
>
> df = data.frame(a=1:5,
>
> geom=c("0105000020D70800000100000001020000000C000000003BA049F49B2E4100667C2F07460F410012BC05F09B2E4100303670E4480F410019849E1D9C2E41007A13FB1B4C0F4180AFF579479C2E41005C30996E4E0F41001E3A23509C2E4100D0F0A76A500F41803BD015919C2E41004A1AFBA7540F4180024687D89C2E4100BA8183FD560F418067F48E3D9D2E410064EAE9D4590F4100852B29879D2E41007AD06D445C0F410053C7ADB99D2E4100D614CECE5F0F41006A378D0F9E2E4100E6D2D363620F4180781D0D679E2E41007829482A660F41",
>
>
> "0105000020D70800000100000001020000000200000080048144E38A2E41001E996E65F20A41805C14EE428B2E41005617618EF50A41",
>
>
> "0105000020D70800000100000001020000001000000000BCB1D1E4422E4100849F4AD0080B4100488B8FF4422E410068152BB9080B4180EAB54700432E41005C2514B3080B4180AA3AE40C432E410064D20EED080B418070941924432E410040A7A9C8090B41804CCB531B432E4100404D67F30A0B41002BC5321F432E410018CD40860B0B4180A9BACC4D432E4100F28879DF0C0B41001AD40E5C432E4100926C30C40D0B4100D441C34C432E41008CA36CE60E0B41804AC53247432E4100C6690DEF100B4100301C7F5A432E4100D2A6CAFB110B41008ACBB368432E41005C8E4620120B418015BE847C432E4100E00A9720120B4100810557AC432E4100F21844CA110B4100EFBB0ADF432E4100CCA9429A110B410",
>
>
> "0105000020D708000001000000010200000003000000009875DF60AC2D4100606296BDAC07410037DB98F1AC2D41001C5EFC79AA074180A23112E1AA2D41001E0E5F18A20741",
>
>
> "0105000020D708000001000000010200000004000000809F59A9A8E02D41009EACC82A130841807FA2D79DE02D410092AB7B64130841007843FF9CE02D41003434DFAE130841805FF302D9E02D41002AC2FE9C180841"))
>
> On Tue, Oct 15, 2019 at 3:30 PM Edzer Pebesma <
> edzer.pebesma using uni-muenster.de>
> wrote:
>
> > Without sharing a (minimal) reproducible example, it is unlikely that
> > someone else can help you find out whether this points to a problem in
> > your data, or in the software.
> >
> > On 10/15/19 2:31 PM, argunaw . wrote:
> > >   When I run this, I get the following error:
> > >
> > > *Error in CPL_sfc_from_wkt(x) : OGR error*
> > >
> > > When I run it on my data, I get the same error.
> > >
> > > On Tue, Oct 15, 2019 at 2:16 PM Edzer Pebesma
> > > <edzer.pebesma using uni-muenster.de <mailto:edzer.pebesma using uni-muenster.de>>
> > > wrote:
> > >
> > >     You may try something along these lines:
> > >
> > >     # read data.frame with read.csv; here, we create an example by
> hand:
> > >     df = data.frame(
> > >                     a = 1:3, b = 3:1, geom = c("LINESTRING(0 0, 1 1)",
> > >     "LINESTRING(1 1,2
> > >     2)", "LINESTRING(5 5,6 6)")
> > >     )
> > >
> > >     library(sf)
> > >     sf = st_sf(df, geom = st_as_sfc(df$geom))
> > >     sf
> > >
> > >
> > >     On 10/15/19 1:58 PM, argunaw . wrote:
> > >     > I'm not sure how it was exported from postgis- the person who
> gave
> > >     me the
> > >     > file wasn't the one who downloaded it unfortunately.
> > >     >
> > >     > The file is a line file of roads. The files main columns are road
> > ID
> > >     > numbers (type integer) and the geometry column (type geometry,
> > >     long strong
> > >     > of letters and numbers). Only the IT admins where I am have the
> > >     postgis
> > >     > load/import tools in the pgadmin/sql interface. The rest of us
> can
> > >     download
> > >     > from sql and create new tables from other sql databases, but not
> > >     create a
> > >     > new table from a csv file.
> > >     >
> > >     > On Tue, Oct 15, 2019 at 1:11 PM Alex Mandel
> > >     <tech_dev using wildintellect.com <mailto:tech_dev using wildintellect.com>>
> > >     > wrote:
> > >     >
> > >     >> On 10/15/19 8:43 AM, argunaw . wrote:
> > >     >>> Hello Everyone,
> > >     >>>
> > >     >>> I have a csv file with a postgis "geometry" column. I've been
> > >     trying to
> > >     >>> import it in to R as a SF file, with the goal of exporting it
> to
> > a
> > >     >> postgis
> > >     >>> database, but to no avail. I've used the following methods:
> > >     >>>
> > >     >>> 1. file <- st_read("name.csv", stringsAsFactors=F,
> > >     geometry_column=geom)
> > >     >>>
> > >     >>> 2. file <- fread("name.csv", headers=True)
> > >     >>>     file <- st_as_sf(file)
> > >     >>>
> > >     >>> How can I import a csv with a postgis "geometry" column in to R
> > as a
> > >     >>> spatial/SF object?
> > >     >>>
> > >     >>>       [[alternative HTML version deleted]]
> > >     >>>
> > >     >>> _______________________________________________
> > >     >>> R-sig-Geo mailing list
> > >     >>> R-sig-Geo using r-project.org <mailto:R-sig-Geo using r-project.org>
> > >     >>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> > >     >>>
> > >     >>
> > >     >> Can you paste an example somewhere, is it binary data or some
> > kind of
> > >     >> plain text column? Do you know how it was exported from Postgis?
> > >     >>
> > >     >> If it's a dump from a postgis database did you try loading the
> > table
> > >     >> directly to postgis with it's own load/import, or sql tools?
> > >     >>
> > >     >> Thanks,
> > >     >> Alex
> > >     >>
> > >     >
> > >     >       [[alternative HTML version deleted]]
> > >     >
> > >     > _______________________________________________
> > >     > R-sig-Geo mailing list
> > >     > R-sig-Geo using r-project.org <mailto:R-sig-Geo using r-project.org>
> > >     > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> > >     >
> > >
> > >     --
> > >     Edzer Pebesma
> > >     Institute for Geoinformatics
> > >     Heisenbergstrasse 2, 48151 Muenster, Germany
> > >     Phone: +49 251 8333081
> > >     _______________________________________________
> > >     R-sig-Geo mailing list
> > >     R-sig-Geo using r-project.org <mailto:R-sig-Geo using r-project.org>
> > >     https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> > >
> >
> > --
> > Edzer Pebesma
> > Institute for Geoinformatics
> > Heisenbergstrasse 2, 48151 Muenster, Germany
> > Phone: +49 251 8333081
> >
>
>         [[alternative HTML version deleted]]
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

	[[alternative HTML version deleted]]

More information about the R-sig-Geo mailing list