Fails to recognize plain vanilla TIGER 2024

[uhop]

I loaded TIGER 2024 using the latest PostGIS and official instructions. From the SQL side the geometry works and I can do geographic searches as expected. Yet pg_tileserv fails to discover tables I need and there is no documented way to force it. I expected that tiger.state, tiger.county, tiger.cousub, tiger.place would be surfaced, in fact only empty tiger.tabblock20 is listed as visible.

As per instructions I ran (slightly modified) snippet:

SELECT
	nspname AS SCHEMA,
	relname AS TABLE,
	attname AS geometry_column,
	postgis_typmod_srid (atttypmod) AS srid,
	postgis_typmod_type (atttypmod) AS geometry_type
FROM
	pg_class c
	JOIN pg_namespace n ON (c.relnamespace = n.oid)
	JOIN pg_attribute a ON (a.attrelid = c.oid)
	JOIN pg_type t ON (a.atttypid = t.oid)
WHERE
	relkind IN('r', 'v', 'm')
	AND typname = 'geometry';

It does show the problem (excerpt):

schema	table	geometry_column	srid	geometry_type
tiger	county	the_geom	0	Geometry
tiger	state	the_geom	0	Geometry
tiger	place	the_geom	0	Geometry
tiger	cousub	the_geom	0	Geometry
tiger	tabblock20	the_geom	4269	MultiPolygon

Notice that srid is 0 for all data but tabblock20 for no apparent reasons.

All troubleshooting steps didn't work for me.

Yet looking at geometry_columns I see (excerpt):

f_table_catalog	f_table_schema	f_table_name	f_geometry_column	coord_dimension	srid	type
postgres	tiger	county	the_geom	2	4269	MULTIPOLYGON
postgres	tiger	state	the_geom	2	4269	MULTIPOLYGON
postgres	tiger	place	the_geom	2	4269	MULTIPOLYGON
postgres	tiger	cousub	the_geom	2	4269	MULTIPOLYGON
postgres	tiger	tabblock20	the_geom	2	4269	MULTIPOLYGON

The troubleshooting steps usually about changing this table and updating data. As you can see everything looks fine in this department. Maybe the detection step should be different.

Just to be thorough I tried Martin and it does show correctly 4 tables I actually need and granted permissions for: state, county, cousub, place. So it is possible to do and works with the same security settings.

It would be nice if the plain vanilla TIGER data worked immediately out of the box.

Please let me know, if I can help to fix this problem and improve the overall DX.

[pramsey]

Start by examining your data. It looks like the column metadata does not include an SRID, but does the geometry itself carry an SRID? What does ST_Srid(the_geom) LIMIT 1 return for all the tables? I think the difference between geometry columns and the tileserv metadata queries is that geometry columns actually pulls the SRID of the first geometry in the table, in the case when the metadata is missing. It might be that we have to do that, or alternatively lean on geometry_columns, not sure if we could just delegate there instead.

[uhop]

They all return 4269.

Example:

SELECT public.ST_Srid(the_geom) FROM tiger.place LIMIT 1;

Returns:

st_srid
4269

[uhop]

Obviously, tabblock20 doesn't return anything: it is empty. (I tried it just in case).

[pramsey]

So, it would be nice if the TIGER import correctly set the SRID of the column (@robe2), I wonder why that doesn't happen. Strange that it gets set on tabblock but not on the other columns.

[uhop]

OK. Please tell me how to do it manually. What table should I modify and how?

[pramsey]

You can manually fix your proximate problem just be altering the table definition.

ALTER TABLE foo ALTER COLUMN the_geom TYPE Geometry(Point, 4269)

[uhop]

Well, it is not a Point. Usually it is a different kind, like Multipolygon.

[pramsey]

Mix in the type you need as necessary :)

[uhop]

It helped. The code:

ALTER TABLE tiger.state ALTER COLUMN the_geom TYPE public.Geometry(Multipolygon, 4269);
ALTER TABLE tiger.county ALTER COLUMN the_geom TYPE public.Geometry(Multipolygon, 4269);
ALTER TABLE tiger.cousub ALTER COLUMN the_geom TYPE public.Geometry(Multipolygon, 4269);
ALTER TABLE tiger.place ALTER COLUMN the_geom TYPE public.Geometry(Multipolygon, 4269);

It did fix the test SQL code (posted before). Now the JSON looks correct:

{
  "tiger.county": {
    "type": "table",
    "id": "tiger.county",
    "name": "county",
    "schema": "tiger",
    "description": "",
    "detailurl": "http://localhost:7800/tiger.county.json"
  },
  "tiger.cousub": {
    "type": "table",
    "id": "tiger.cousub",
    "name": "cousub",
    "schema": "tiger",
    "description": "",
    "detailurl": "http://localhost:7800/tiger.cousub.json"
  },
  "tiger.place": {
    "type": "table",
    "id": "tiger.place",
    "name": "place",
    "schema": "tiger",
    "description": "",
    "detailurl": "http://localhost:7800/tiger.place.json"
  },
  "tiger.state": {
    "type": "table",
    "id": "tiger.state",
    "name": "state",
    "schema": "tiger",
    "description": "",
    "detailurl": "http://localhost:7800/tiger.state.json"
  }
}

Interesting that tabblock20 is gone too, which was my intention.

Thank you for your prompt help and for creating and supporting the great tool!