Search code examples
postgresqlgopostgispostgresql-9.4pgx

PostGIS rows are being inserted as hexadecimal WKT inside Postgres

enter image description hereAs seen in the following function, I am trying to insert rows into the building table. Everything works except that instead of being saved as normal text, the coordinate is saved as WKB (as shown in the screenshot and sample).

func (db Database) SaveBuildings(buildings overpass.Result) error {
    tx, err := db.pool.Begin(context.Background())
    if err != nil {
        return err
    }
    defer tx.Rollback(context.Background())
    for _, building := range buildings.Nodes {
        _, err = tx.Exec(context.Background(), `INSERT INTO building (coordinate, type, variation) VALUES(ST_SetSRID(ST_MakePoint($1, $2),4326), $3, $4)`, building.Lon, building.Lat, "industrial", "brick")
    }
    if err != nil {
        return err
    }
    err = tx.Commit(context.Background())
    return err
}

Building table sample

uuid                                |coordinate                                        |type      |variation|
------------------------------------|--------------------------------------------------|----------|---------|
dff2f9a3-03f1-477c-8b3e-9c6708d97519|0101000020E610000028637C98BDE63B4092301D84CAE04740|industrial|brick    |
7b6b619a-8c0f-4a3d-b098-d878a2b1d152|0101000020E6100000EFE2FDB8FDE43B4085AC133C2AE04740|industrial|brick    |
efd68a88-a349-4f1b-9406-23920031701f|0101000020E6100000D7C3F24295E53B40A538FD8F02E04740|industrial|brick    |
3b9bfc69-9dbc-42f5-a80d-615b972da3a2|0101000020E610000085E39E5335EA3B40B1FD648C0FE14740|industrial|brick    |
19e798d9-5ce7-47e4-aea0-0eb039805269|0101000020E6100000CD48731A58E73B40A76384A6DBDF4740|industrial|brick    |
c99ab27c-a205-460c-8371-8306895fcd8b|0101000020E610000091FF136DD8E63B40E5492DEF05E04740|industrial|brick    |
f9aabf00-c3aa-4a45-8c15-5b538390b807|0101000020E6100000E2395B4068E53B403A98A839D4E04740|industrial|brick    |
a866e708-6438-4524-b257-e3f7f84b7027|0101000020E610000077B6ECB545E63B400B067C235FE04740|industrial|brick    |
849add05-4384-492a-baf9-5d151751e8c5|0101000020E61000002B78C02B38E83B4098512CB7B4E04740|industrial|brick    |
ae8d7de0-10d1-4cf5-b4ef-7c483e9f6669|0101000020E61000009D9E776341E73B40A3923A014DE04740|industrial|brick    |
c3a908d7-49d1-4f95-a5d2-b9aa06a44768|0101000020E61000001E0B1BAF8AE53B403BE702F280E04740|industrial|brick    |
d86c441c-fcea-4b53-883e-e7651f8f7afd|0101000020E6100000EDA7B51FDFE43B403999B85510E04740|industrial|brick    |
d66543a3-f6a9-47dc-b47d-2e027b62d24d|0101000020E6100000A15F00868AE63B40113A43CC80E04740|industrial|brick    |
6d0ca88e-b645-4880-ad9a-a12dee11cb9c|0101000020E61000007E5704FF5BE53B403C9FA63BF4DF4740|industrial|brick    |
acf63b3f-d86c-478a-9267-97407103b490|0101000020E6100000ACADD85F76E53B40C0D02346CFE04740|industrial|brick    |
381995fe-c6ed-4916-a2a3-7def605ef30e|0101000020E6100000A02EF76E98E53B40267909A936E14740|industrial|brick    |
0279d9cc-0521-4c94-8132-2276f9dccb0f|0101000020E6100000383A9D1A1EE73B407F57BA7141E04740|industrial|brick    |
ec148677-783e-404a-9504-ac697dceb081|0101000020E6100000FBF14D89DAE43B40514942C7B3E04740|industrial|brick    |
680a6d28-34e5-4639-94df-32c00a9aceaf|0101000020E610000002678412C1E93B406353F87D49E14740|industrial|brick    |
a4f70282-c792-4ea0-b0af-cf7adea7904e|0101000020E6100000A651370653E93B40749593F540E14740|industrial|brick    |
c3f88db7-9b3c-44e8-b302-d45458389154|0101000020E610000041295AB917E83B40A4D1D336B4DF4740|industrial|brick    |
56021f76-f3df-4275-8c65-f1ad50053037|0101000020E610000057E47C563EE63B40FCD52D4CF0DF4740|industrial|brick    |
e2a1265a-6aa2-4b84-a5b6-b66868f7ba5d|0101000020E6100000330285D5B3E93B403E7B2E5393E04740|industrial|brick    |
bc423d77-ea20-4883-9b4f-60b3fc85961a|0101000020E61000005F07CE1951E43B40338573B279E04740|industrial|brick    |
62e982c2-3119-4962-81b3-219c0b43a02c|0101000020E6100000D322916C64E53B40CD4B0F54D7DF4740|industrial|brick    |
ad728221-d528-4856-bff8-80a5ac48956a|0101000020E610000061342BDB87E43B401C78B5DC99DF4740|industrial|brick    |
bd5cf4bd-2ba8-4404-9aee-923e9f9dbcc9|0101000020E6100000980BA6AB85E53B402134CC1A06E04740|industrial|brick    |
db981d6e-39a7-4fd9-8526-dcdbe9c2287a|0101000020E610000095DCBCCC0BE53B40F6FC7ACF92DF4740|industrial|brick    |
e7a9863c-0599-45ba-a0de-fd55636cb981|0101000020E6100000FD5E549CB4E43B4050D37833B4DF4740|industrial|brick    |
c6bd0ab2-33fe-4c10-9630-4dba1519aac0|0101000020E610000036EA211ADDE73B40139ED0EB4FE04740|industrial|brick    |
bd78f7ce-ff80-43b0-94a5-7c0d53c88e72|0101000020E61000000220387870E73B40C003A84EACE04740|industrial|brick    |
Solution

  • WKB is just the representation of the processed points by Postgres. It's actually good! Imagine that you stored all these points as text, then how would you perform calculations on them, like clustering or calculating the distances between them?

    To get their values in the way you want, just use

    SELECT uuid, ST_ASTEXT(coordinate) as coordinate_wkt
FROM building