webviewer doesn't work as-is

rotten commented 1 year ago

I tried the arcgis web viewer as you have described in the README:

https://www.arcgis.com/home/webmap/viewer.html?url=http://localhost:8080/pg/myschema.mytable/FeatureServer/

Unable to establish a secure HTTPS connection to the layer. The layer, myschema.mytable, cannot be added to the map. The server appears to be listening, and appears to be configured as described including the PG_OBJECTID environment variable.

I suspect this is a CORS issue of some sort. Anyway, I wanted to point out that it doesn't work directly as described.

I think I have everything set up correctly. What are some other simple local urls I can check to see if I get the appropriate geojson back? (or anything at all)

rotten commented 1 year ago

Well, firstly, the local URL is no longer as described in the readme, it should be something like

http://localhost:8080/pg/rest/services/myschema.mytable/FeatureServer/0/query?where=<something>

That exposed issues with my database that I'm still figuring out. I suspect the arcgis viewer will work once I get it resolved, but meanwhile wanted to point out the different path than found in the documentation here.

rotten commented 1 year ago

Ok, the problem was the geometry columns table showed the srid for the relevant column in mytable to be 0 which is not a legal srid. Once I hacked the query in src/db/sql/getGeometryColumnName.sql to return a default srid, the endpoint worked. I'll probably fork and do a PR for this repo if we continue down the path of using Koop. I'll update the docs in the same PR. I'm leaving this issue open for the moment because the docs are wrong.

doneill commented 1 year ago

The url pattern works for me, please share the changes you required to get it to work. Below is an example of a table, with schema.table not requiring a query parameter

rotten commented 1 year ago

I don't have the code I was using when testing back then, because I gave up on using koop since it would require a significant fork for our use case, but I believe I simply changed the query from:

select f_geometry_column, srid  from geometry_columns ...

to something like

select f_geometry_column, case when srid = 0 then 4326 else srid end as srid from geometry_columns

jjhi11 commented 12 months ago

I cannot get the endpoint to work. I get this error

error TypeError: Cannot read properties of null (reading 'f_geometry_column') on getGeometryColumn(ugs_general, ugs_gw_sites) - Error: TypeError: Cannot read properties of null (reading 'f_geometry_column') on getGeometryColumn(ugs_general, ugs_gw_sites)"

WHen trying this url https://ugs-koop-umfdxaxiyq-wm.a.run.app/pg/rest/services/ugs_general.ugs_gw_sites/FeatureServer

doneill commented 11 months ago

@jjhi11 What is the name of your databases geometry column and srid?

jjhi11 commented 11 months ago

@doneill wkb_geometry and 4326

doneill commented 11 months ago

Those are the values returned for the schema.table you are trying to view when querying the database directly? E.g. SELECT * FROM geometry_columns;

jjhi11 commented 11 months ago

Um not too sure on that. Here is a sample of what is returned on a * query to the table. Screenshot 2023-10-11 at 6 15 07 AM

rotten commented 11 months ago

This is what our geometry_columns table looks like:

> select * from geometry_columns;
 f_table_catalog | f_table_schema |          f_table_name      | f_geometry_column | coord_dimension | srid |   type
-----------------+----------------+----------------------------+-------------------+-----------------+------+----------
 our_fine_db         | public         | my_geolocations_table  | centerpoint       |               2 |    0 | GEOMETRY
 our_fine_db         | public         | my_centroids_layer     | centroid          |               2 |    0 | GEOMETRY
 our_fine_db         | public         | my_shapes_layer        | shapes            |               2 |    0 | GEOMETRY

jjhi11 commented 11 months ago

I apologize. Here are my geometry_columns.

Screenshot 2023-10-11 at 8 30 25 AM